Populating Asteridex from Exchange Server

[slickrock22]

I have been using the MySQL based Asteridex table with a custom Lookup Source in FreePBX using a Query String

SELECT name FROM user1 WHERE out = '[NUMBER]'

For some reason when I just upgraded my system from Trixbox 1.2.3 to PBX in a Flash that no longer worked. I was just about to utilize some of my FreePBX support credits when I noticed the CallerID Superfecta! Once again Ward to the rescue. While the setup uses source type HTTP it effectively accomplishes what I was doing before.

The one major benfit for my company is that I periodically export the Global Contacts from my MS Exchange server and load to a MS Access Database for appending to the [user1] table in the Asteridex DB in MySql. These are the steps.

In MS Outlook
1. Select File->Import and Export...
2. Select Export to a file
3. Click Next
4. Select Comma Separated Values (Windows)
5. Select Microsoft Excel
6. Select Contacts (Outlook PCS**See Below)
7. Select a file destination
8. Select Finish

You should have now successfully exported your contacts. **If you are using a MS Exchange server with corporate shared contacts you will need to install a program called Outlook PCS.( Pocket Contact Synchronizer). Now I believe it is only for Outlook 2002, but I am using with 2003 for what it is worth. The objective of the tool is to allow users to synch Global Contacts from the exchange server with your personal contacts in Outlook. This is the only way you can effectively export exchange based global contacts that I am aware of. Once the Synch is done you begin at step 1 above. When you done with the export and want to get the additional 1K-5K of addresses out of your personal contacts you can always view by categories in Outlook and delete all of those that are in “Outlook PCS” category and take your personal contacts back to normal.

Now you will need to create a MS Access DB and connect to the MySQL database by creating an ODBC connector

In Windows XP

• Select Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
• Select the “System DNS” tab
• Select Add
• Select MySQL ODBC 3.51 Driver (if this is not an option download and install from http://dev.mysql.com/downloads/connector/odbc/5.1.html - note I use the 3.51 and it looks like the latest is 5.1)
• Datasource: Whatever you want to call it, Description: irrelevant, Server: IPAddress of PBX, User: root, Password: [your password], and if all of that is correct and it connect you will select Asteridex as the database in the dropdown. If it does not drop down, your userid, password or ipaddress is wrong.
• Now open up your MS Access (or my DB if I get it uploaded just use the linked table manager to repoint my user1 table to your own created above) and if your DB select File -> Get External Data -> Link Tables
• Select Files of Type: ODBC Databases ()
• Next Select the Machine Data Source tab
• Select the name you created for the Datasource in bullet 5 above
• Select the table user1

No that you have “Linked” the user1 table from Asteridex you should be able to click it and browse the content. I deleted all of the stuff that came with it natively. An be careful if you use my DB as I think it blows away the entire user1 contents and re-appends each time. Also because MS Outlook stores different phone numbers for each contact I created queries that append each number for the contact with an appended Mobile, Office or Home at the end of the First Name and Last Name of the contact. Also everything is run by macros except for the final append which I do manually using “AppendToPBX” append Query.

Good luck and send me an email if I can answer any questions.