First, let’s take care of the Ultimate Question of Life, The Universe, and Everything, since this one is really, really easy. Well, the answer is 42.
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).
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.ResourceGROUP BY ResourceId, UserAtHost)resON hud.OwnerId=res.ResourceIdORDER BY "Last Logon" DESC
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 JOINRTC.dbo.Resource r ON c.OwnerId = r.ResourceId
And here’s a picture of the results obtained.
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 cINNER JOIN RTC.dbo.Resource r ON c.ResourceId = r.ResourceIdWHERE (c.OptionFlags & 128) = 128
In my test environment, I have the following “UC Enabled” users:
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”.
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.
Is it possible to run a query that will return either which options users have for handling un-answered voice calls, such as send to another user, to a group, to a PSTN number or voice mail. Or failing that just a list of users with no forwarding set at all ?