Getting Lync 2013 Address Book Data from SQL

I recently ran into a problem with an invalid phone number showing up on the Lync contact card for a user.  The Lync contact card pulls info from a number of sources – Active Directory, SharePoint, personal contacts, phone numbers defined directly in the Lync client, etc.  In order to narrow down the problem and find where the bad phone number was stored I wanted to see what Lync had in the address book database for the user.

Address book data is stored in the RTCab database, however, the actual data is spread amongst multiple tables.  The data I was interested in for this case is in the dbo.AbAttributeValue table.  I started with the following query to find out what the UserID for this particular user is:

Select * from [rtcab].[dbo].[abattributevalue] where Value like ‘%<name>%’

*NOTE: <name> is replaced with the name you are looking for.

DBUserId

This query returned all entries with the value of “Sand”, which is the last name I was looking for.  Depending on the number of users, you may need to get more granular with your search string.  After locating the entry needed, I then noted the assigned UserID and executed the following:

Select * from [rtcab].[dbo].[abattributevalue] where UserID = ‘<userid>’

*NOTE: Again, <userid> is replaced with the userid found during the query earlier.

Voila! This query returned all data in the address book related to this specific user.

To take this one step further, you can use the query below for a clean display of each attribute name and value associated with it for the user.  *NOTE:I’ve only included a snippet of the results to remove identifying information.

Select Name,Value from rtcab.dbo.AbAttributeValue,rtcab.dbo.AbAttribute where AttrId=Id and UserId='<userid>’

Query1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s