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.
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>’