Improve Lync Contact Search without Sharepoint

Do you ever get the feeling that searching for users in Lync could be a whole lot easier?

A friend pointed out that he couldn’t search for a particular department in a newly deployed Lync Server 2013 environment, yet when he searched with a name, the department information showed up.

See below example – Search for a contact on Lync Server 2013, and then see the information returned, but you can’t use it to search.

Lync 2013 - Contact Search

This appears to be by design, after all if you wanted to search on a persons’ attributes, then you’d be better served with the Skills Search Lync integration with SharePoint. But not every company has SharePoint, and certainly not every company has SharePoint setup correctly with MySite filled-in and up to date for every user.

I know that without SharePoint, you still get a decent experience due to Outlook, as Lync pulls people out of your Contacts Folder in it’s results. But that doesn’t help the Lync Mobile client. I’ve heard of some companies without Enterprise voice who disable Outlook Contact search in the Client Policy because users were searching for contacts, that they couldn’t IM or Lync Call.

Prerequisites

This is only possible by forcing the Lync Clients to use only the Address Book Web Services, and not to download and cache it from the server.

How the Lync Address Book is Populated

I won’t go into too much detail as this is documented fairly well. But, as a quick reminder, Lync has a two stage process for first filtering and importing Active Directory Objects into it’s database (handled by the User Replicator service), then the Address Book Service creates the actual address book file based on that information.

Filter and Import

Update-CsUserDatabase runs a task that filters the Active Directory Objects, and which attributes get imported into the AbUserEntry table, based on the AbAttribute table. By default this is run every 5 minutes. Whereas the Address Book file is updated every 24 hours by default.

AbAttribute holds a set of instructions telling Lync which AD Attributes are to be read from users and contacts, and how it’s stored (i.e. as a string, only if it starts with tel: or smtp:, as binary, as a multi-value, or normalised before stored etc) or how that attribute is used to filter or understand the AD Object i.e. is it a group.

A good example of the filtering is when a user has been hidden from the Global Address List in Exchange, this means if the Active Directory Attribute msExchHideFromAddressLists is set to True, then User Replicator service (by default) has a flag which states that object will be excluded from the Lync Address Book.

Additional Normalisation for Keypads

I noticed something I thought was interesting, Lync pre-converts these attributes imported from AD as text (if the Flags specify it), AND to E.161 standard ‘touch tone’ combinations. For example when using a keypad to type ‘graham’, it would be 472426 (4 = GHI, 7 = PQRS, 2 = ABC and so on).

Here’s an example of how that would appear in the AbAttributeValue table.

AdPartitionId UserId AttrId Value Dial
2003 5032 1 graham 472426

If you’ve ever used a Lync Phone Edition device to search for a contact, you’ll have already used this.

How the Lync Address Book is Searched

Unfortunately, like most things, the names can be deceptive. For example the Group Expansion Web Service is the one called for searching for contacts, and if you’re looking at OCSLogger.exe then it’s called DLX (Distribution List eXpander).

Taking a little look at how the Lync Clients searches for contacts shows up the following Web Service request… (Simplified for readability – Envelope Header contains SAML Assertion as the Web Service Ticket for authentication – for more information my previous article LyncDiscover and Auto Discovery Deeper Dive explains how this works and what’s going on).

As you can see the <SearchList> contains a comma separated list of fields to search against, this is directly affected by Set-CsClientPolicy -SearchPrefixFlags PowerShell cmdlet – and you can choose any combination of…

  • Primary Email Address (mail)
  • Email Alias (mailNickname)
  • All Email Addresses (proxyAddresses)
  • Display Name (displayName)
  • First Name (givenName)
  • Last Name (sn)
  • Telephone Number (telephoneNumber)
  • Mobile Number (mobile)
  • SIP Address (msRTCSIP-PrimaryUserAddress)

But the <ReturnList> has many more fields, such as Job Title, Department, Description etc.

Wouldn’t it be nice if we could search in any of those too? Well, er, yes.. otherwise I wouldn’t have written this.

From Web Service to SQL Server

I’m only talking about the ‘WebSearchOnly’ side of this (not the downloaded copy), and it’s fairly simple as you saw above. The SOAP XML is nice and short. Once the request has reached the Lync Server, the SQL Server has a few stored procedures to use based on what, and how you are searching;

  • RtcAbGetAbDataInTenantByExactMatchAttributeDialValue
  • RtcAbGetAbDataInTenantByExactMatchAttributeValue
  • RtcAbGetAbDataInTenantByPrefixMatchAttributeDialValue
  • RtcAbGetAbDataInTenantByPrefixMatchAttributeValue

The difference between them being ‘ExactMatch’ vs ‘PrefixMatch’ and ‘Value’ vs ‘DialValue’.

Above we saw that Lync automatically requests <Verb>BeginsWith</Verb> that translates to ‘PrefixMatch’, and <FromDialPad>False</FromDialPad> means it is a text string rather than E.161 digits.

When the Client Policy is set to WebSearchOnly – Lync uses the PrefixMatch stored procedure to provide substring results (Stored procedure uses ‘like’).

When the Client Policy is set to WebSearchAndFileDownload – Lync uses the ExactMatch stored procedure (Stored procedure uses ‘=’).

Stored Procedure

Lets look at the most common combination.. text prefix search, and therefore; RtcAbGetAbDataInTenantByPrefixMatchAttributeValue.

Amongst the parameters this stored procedure is expecting, are the following…

  • @_AbQueryAttributeCount – this is a ‘smallint’ and should contain the number of attributes you’ve passed.
  • @_AbQueryAttributes – this is declared as an ‘image’ and took a bit of tinkering to understand how it’s used (see below).
  • @_AbQueryAttributeValue – this is an ‘nvarchar(256)’ and holds the value we’re searching for.
  • @_MaxResultNum – this is an ‘int’ and should be the maximum number of successful results that the server will return.

There are also @_TenantId and @_GroupingId which are both ‘uniqueidentifier’, this is just how Lync segregates the users from multiple tenants, or within different ‘groupings’ within a single-tenant – so you don’t end up seeing users you shouldn’t.

I had to do a little reverse engineering to work out how this was expecting to receive the @_AbQueryAttributes, the answer was in how the stored procedure ‘de-constructed’ the ‘image’ value.

‘row’ is actually the result of a SELECT on a table called sequence which just contains field called ‘Number’ which has a thousand rows starting from 0 going up to 999. I guess a little programmatic corner cutting never hurt anyone.

So, all that does is split up AbQueryAttributes into 4 byte chunks and CASTs that SUBSTRING to an ‘int’… and that ‘int’ matches up with the ID column of the AbAttribute table we saw above.

For givenName, sn, and company, that would be 1, 2, and 6. with 4 byte padding and in hex that becomes 0x00000001, 0x00000002, and 0x00000006 in hex. putting that all together that becomes 0x000000010000000200000006.

Lets Make Something Happen

Knowing that the AdAttribute table contains IDs for each field in the address book, and knowing that the stored procedure is passed a concatenated list of those IDs, I had a thought.

“What’s the harm in adding a few extra Attributes to the end of that list before it’s passed to the SQL Statement”.

So I added the following lines immediately before the first SELECT of the stored procedure.

Here I’ve assumed you’d want to add Job Title (Attribute ID 4), Company (Attribute ID 6), Department (Attribute ID 16 – 0x10 in hex), and Description (Attribute ID 17 – 0x11 in hex) to the list of attributes being searched (which if you remember, is passed by the client, derived from the Client Policy’s SearchPrefixFlags entry).

The first line adds my 4 additional attributes to the end of the incoming attribute list as supplied by the client application. It does this by converting to VARBINARY and combining them together (when adding VARBINARY values the ‘plus’ sign has the following effect… 4+7 = 47).

The second line increments the AttributeCount by the number of new attributes I’ve plonked onto the end of the list. In this case it’s 4.

The Altered Stored Procedure

The new stored procedure in it’s entirety is as follows… (you would need to alter all 4 of the stored procedures to cover all search possibilities)

For added flair, I also added a 3rd SET line to prefix “%” to the Value being searched for, so it doesn’t need to start with it. Allowing “Consultant” to match “Technical Consultant” as well as “Project Consultant”.

Recap

With two extra lines added to the stored procedure which ‘does the dirty’ and injects the additional search attributes, the results are spectacular across the board…

2013-Contact-Search-for-stuff

2013-Mobile-Search

2011-Contact-Search-for

Don’t Try This At Home

Is this supported? I highly doubt it.

For piece of mind however, there is no change to the database schema, no change to the amount, nor type of parameters being passed to the stored procedure, nor any changes to the SQL Statements themselves.

Although, I would recommend you think long and hard before you go changing anything like this in your production environment.

And lastly, any database update will destroy your changes, and revert back to the default behaviour, so keep a backup of anything you change.

Tweet about this on TwitterShare on LinkedInShare on Facebook
Pin on PinterestShare on Google+Digg thisShare on RedditShare on StumbleUponEmail this to someone

About Graham Cropley

Working as a Senior Consultant for Skype for Business, Exchange, and Office 365.

Leave a Reply

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