Updating SIP addresses in Windows Sharepoint Services 3.0
One of the improved features of Windows Sharepoint Services 3.0 is enhanced presence awareness using Microsoft’s Live Communication Server. If your company is using LCS, Office Communicator, and Sharepoint, you can see any user’s Communicator status in Sharepoint any time there’s a link to their user profile; for instance the links for the created by and last modified user for any item:
Clearly this can come in handy, and integrates with AD, Outlook and Office Communicator:
In my organization, however, we ran into a small problem. When we first deployed Sharepoint we hadn’t started working on a Live Communications Server infrastructure yet and the SIP address that’s the central identifier in LCS was populated with the contact’s email address. This is how most of the users in Sharepoint were added. After Sharepoint was largely populated with users we started working on LCS and changed the schema for SIP addresses in LCS, updating all of the users in AD in the process. This created a mismatch between the AD/LCS SIP address for each user and that user’s SIP address in Sharepoint which meant that Sharepoint had no way of retrieving presence data for those users. New users worked perfectly fine, but existing ones were broken, from an LCS point of view.
Looking briefly at the Sharepoint v3 database, it wasn’t apparent where the SIP address for a user is stored - there’s no field for SIP address in the UserInfo table, seemingly the logical place for such a field. None of the other tables in the database seem to contain such a field or to map to a user in such a way that they’d be likely to hold such data. A post to Microsoft’s Managed Newsgroups gleaned nothing of use - the Microsoft Employee who responded was also of the opinion that the data was contained in the UserInfo table.
Faced with the prospect of updating 445 users by hand, I ended up running a SQL Profiler trace while updating a user’s SIP address to see if I could discern where that data was being stored in Sharepoint. Parsing that turned out to be quite a feat; Sharepoint uses a multilevel stored procedure to update that information, updating the list based on a dataset row returned from a different stored procedure, but the long and short of it, as described in this MS managed newsgroup post, is that this level of user information is stored in a Sharepoint List called “User Information List”. I retrieved the ListId from the trace log and executed a single TSQL query to update the value of the nvarchar5 field, which stores the SIP address information in this list.
Here’s the query I ended up running:
UPDATE AllUserData
SET [nvarchar5] = LOWER(LEFT(nvarchar1,1) + SUBSTRING(nvarchar1, CHARINDEX(’ ‘, nvarchar1) + 1, 7) + ‘@etcconnect.com)
WHERE tp_ListId = {User_Information_list_ID}
{User_Information_list_ID} must be replaced with the appropriate id for that list in any given Sharepoint installation; this SQL Query will find that row:
SELECT * FROM AllLists WHERE LOWER(tp_Title) LIKE ‘%user information%’
The rest of the query extracts the properly formatted SIP Address from the data in the User Information List. In our case that was:
[first character of first name][first 7 chars last name]@[domain].[tld]
resulting in an 8 or fewer character name followed by our domain and tld (”etcconnect.com”). The SET clause would need to be changed to compile the correct pattern if that pattern’s not correct.
Perhaps the most important lesson to be learned from this exercise for me was to open my eyes to the wide variety of data that’s stored in lists. The WSS3 data model is fairly simple and easy to grasp, with only about 90 tables almost half of which are for localization. Once you realize that WSS system data is buried in Sharepoint Lists it opens up a whole new horizon of direct database access to the data in Sharepoint, but also reveals how difficult to find some data might be, especially without understanding how Lists are leveraged in the Sharepoint data model.
RSS 2
