The Ultimate Question of Life, The Universe, and Everything (if time permits, some useful SQL queries about OCS users)

Now, on with the useful (so I hope) SQL queries that will let you know a little bit more about your environment.

In order to build valuable SQL queries, one must know the backend data structure that supports Office Communications Server (OCS) 2007 R2.

The TechNet page Storage Requirements has a nice table that enumerates the different databases created on a default OCS installation, whether it’s Standard Edition or Enterprise Edition:

All the information used in the queries resides on these databases. To execute the queries, I’ll use Microsoft SQL Server Management Studio (actually I’ll use the Express Edition, since I have OCS Standard on my test environment).

Query #1 – List users that have logged in OCS

For this query, the needed information resides on the RTCDyn database, more specifically at the dbo.HomedResourceDynamic table, which contains the LastNewRegister field (last logon time) and the ResourceId field (ID of the user).

Since we want to display SIP Addresses and not user IDs, we’ll cross information with the UserAtHost field, that resides on the dbo.Resource table from the RTC database.

This is the SQL query…

SELECT hud.LastNewRegisterTime AS "Last Logon", res.UserAtHost AS "SIP Address" 
FROM rtcdyn.dbo.HomedResourceDynamic hud JOIN
(SELECT ResourceId, UserAtHost FROM rtc.dbo.Resource
GROUP BY ResourceId, UserAtHost)
res
ON hud.OwnerId=res.ResourceId
ORDER BY "Last Logon" DESC

Query #2 – List users that have at least one contact in Communicator

Although adding contacts (or buddies) in Office Communicator is not required to take advantage of all the features provided by OCS, an Administrator might find useful to know which users have at least one buddy in their list of contacts.

The information needed resides on the RTC database, more specifically at the dbo.Resource table. The query looks like this:

SELECT DISTINCT r.UserAtHost FROM RTC.dbo.Contact c INNER JOIN
RTC.dbo.Resource r ON c.OwnerId = r.ResourceId

And here’s a picture of the results obtained.

Query #3 – List users that have Enterprise Voice enabled

OCS stores the configuration of the different user features that are enabled in (at least) 2 locations: in the table dbo.ResourceDirectory, field OptionFlags, on the RTC database; in the Active Directory, in the msRTCSIP-OptionFlags attribute.

In this example I’ll use a SQL query to find that information on the RTC database.

SELECT r.UserAtHost FROM RTC.dbo.ResourceDirectory c
INNER JOIN RTC.dbo.Resource r ON c.ResourceId = r.ResourceId
WHERE (c.OptionFlags & 128) = 128

In my test environment, I have the following “UC Enabled” users:

Query #4 – List users that organized Live Meeting conferences

If someone wants to know the users that have organized Live Meeting conferences, that information is stored on the RTC database, dbo.Conference table, OrganizerId field.

SELECT DISTINCT r.UserAtHost FROM RTC.dbo.Conference c 
INNER JOIN RTC.dbo.Resource r ON c.OrganizerId = r.ResourceId

The following image depicts the results from my test environment:

The answer to this question could also be obtained by using the Resource Kit tool DMInside with the option “List organizers”.

Want more reports?

OCS records much more information and keeps track of several indicators about pretty much everything related with the different forms of communications available to the users. Deploying Monitoring Server will unleash all the potential of OCS reporting.

Curtis Johnstone has written a nice blog post about other reporting possibilities that I strongly recommend you to read.