Looking up published telephone numbers in the database.

Occasionally you run into a situation where you view an individuals contact card and the phone number is wrong:

However, when we view the individuals information in Active Directory, we have the following:

And if we dump the address book file to text, we clearly see that the phone number has been normalized as planned as defined in the Company_Phone_Number_Normalization_Rules.txt file.

So the question are: where is this number coming from and how do we update it?

Hacking the Database

The first thing we need to realize is that the phone numbers that appear on the contact card can come from multiple locations and one of those is the users published information in the database.

All of the database queries are going to be run against the RTCLocal database.  So if this is a Standard Edition deployment, it’s simply, it’s your only front-end server.  If it’s an Enterprise Deployment, you will first need to find the primary server for that users routing group.  That will add a little complexity to this hunt.

So where can we find that information:

CategoryDef Table

The CategoryDef table contains a list of published information.  One of those items will be UserInformation:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [CategoryId],[Name],[Private],[ReplicationFlags],[QuotaTotalSize] FROM [rtc].[dbo].[CategoryDef]

Here we can see a result of the query:

UserInformation should be CategoryID 23 but you should verify just in case.

Resource Table

The second item that needs to be found is the resource ID for the user we are looking for.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ResourceId] ,[UserAtHost] FROM [rtc].[dbo].[Resource] WHERE UserAtHost LIKE ‘sarah@%’

Here, we are searching for any resource with the SIP address that starts with ‘sarah@’.  The result returns ResourceID 1384.

PublishedStaticInstance Table

In this table, we will find all of the published information for all users based on the above category IDs and presence containers.  The following query will narrow this down to the specific user and phone number information:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [PublisherId],[CategoryId],[ContainerNum],cast(cast([Data] as varbinary(max)) as varchar(max)) as Data FROM [rtc].[dbo].[PublishedStaticInstance] WHERE PublisherId = ‘1384’ AND [CategoryId] = ’23’

Where CategoryID is equal to the category for UserInformation and PublisherID is equal to the ResourceID found above.  You can see that we are casting the [Data] column to varchar so we can easily read it.  When we copy the results from the table to notepad, we get the following results:

Here, we can clearly see that the phone number that has been published, is incorrect.

How Does It Get There?

When a user logs into their client, their phone number information can be found on the Tools | Options | Phones tab of the Lync Client.  This information is populated based on information stored in Active Directory and using the rules specified in Company_Phone_Number_Normalization_Rules.txt we normalize that number.  The user than publishes that information into the SQL database for themselves.  So that means:

1. It’s the users client that is responsible for updating the database.  This can be a problem if the user rarely logs into their Lync client (executives sometimes will log in rarely) or use certain 3PIP phones only.

2. The client has issues updating the local address book copy.

(NOTE: As of this writing, there is a current bug with Lync 2013 Enterprise Pools where a delta address book isn’t created correctly.  This can lead to a significant delay in updating the local address book file on the local machine.  Which means, it can delay the updating of published information in the database as well.  There are two workarounds: First, you can go to WebSearchOnly in the client policy.  Second, you can change the address book configuration.  You can find the details here.)

So when fighting contact card information you need to:

1. Ensure you have all of your normalization files in place.  Especially check the Company_Phone_Number_Normalization_Rules for any mistakes.

2. Troubleshoot from the users computer who is showing the wrong number as they are the ones actually publishing the incorrect number.  Start by deleting galcontacts.db and forcing the client to download a new copy of the address book.  Remember, you can control the delay via the registry.

One Last Number

There is one last number you should be aware of.  When you hover over a contacts phone icon in the Lync Client you will see this:

There are two items at play here.  First, the registry has an entry for the last number you called for that contact.  You can check out my old article on controlling click-to-call for more information.  Second, the Lync client also caches telephone information in the SIP_ folder.  If you browse to the C:Users<username>AppDataLocalMicrosoftOffice15.0Lyncsip_user@domain.com folder, you will find the CoreContact.cache file.

If you delete the CoreContact.cache file, the phone number displayed when hovering over the telephone icon will be pulled from the published information and displayed.  You will also see the cache file immediately recreated with this newly discovered information.