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:

pic1

Phone number is actually x4150

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

pic2

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:

pic3

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:

pic4

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:

pic5

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.

pic6

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.

8 comments on “Looking up published telephone numbers in the database.”

  1. Ryan Reply

    HI Richard,

    You do not have to use webquery to get around this problem. We chose to set the set-csaddressbookconfiguration -maxdeltafilesize to 100, thus pull a fresh copy every day.

    • Richard Brynteson Reply

      Ryan – that is a good point. I didn’t offer that as a work around and I should have. I’ll update with a link to your blog on that one!

  2. flinchbot Reply

    Regarding pulling a full address book – it’s a fine idea to just pull the full address book but not so much if you have WAN contention. We’ve buried 3 of our WAN links due to users pulling down full copies of the address book so we were forced to switch to WebSearch only. So far we haven’t noticed any issues at all.

    • Richard Brynteson Reply

      Agreed 100%. If you have a large address book it can cause all sorts of issues. I know MS uses WebSearchOnly because of this.

  3. Ryan Reply

    As far as why information is wrong about a users phone number we’ve hit this and the answer is in my past about “Lync Contact Merge”.

    http://www.ucryan.com/

    Essentially lync pulls data from a lot of locations when talking about the phone numbers.

    Yes, about the download only, yes you need to understand your network. I fortunately am blessed to have a network i can walk upright through!

  4. Ryan Reply

    Sorry for spamming…an example of this is when you give a user a new phone number.

    User Joe Schmoe has phone number 111.111.1111. This is populated in AD/Lync/PRESENCE (i made presence caps for a reason).

    Joe takes a 30 day vacation and is completely logged out of Lync.

    We as nice Lync admins give joe a new number of 222.222.2222 and update this in AD and in Lync, however only Joe Schmoe can update his information in the Presence database. Thus until Joe Schmoe returns from vacation users will continue to see both phone numbers because of how lync “aggregates” what the user see in a contact card when talking about the phone number.

  5. Pingback: NeWay Technologies – Weekly Newsletter #96 – May 22, 2014 | NeWay

  6. Pingback: NeWay Technologies – Weekly Newsletter #96 – May 23, 2014 | NeWay

Leave A Reply

Your email address will not be published. Required fields are marked *