The Ultimate Question of Life, The Universe, and Everything (if time permits, some useful SQL queries about OCS users)
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:
| Database | Type of Data | Location |
| RTC | Persistent user data (for example, ACLs, contacts, home server or pool, scheduled conferences) | Enterprise Edition, back-end database; Standard Edition, Microsoft SQL Server 2005 Express with SP2. |
| RTCConfig | Persistent Office Communications Server 2007 R2 settings | Enterprise Edition, back-end database; Standard Edition, Microsoft SQL Server 2005 Express with SP2. |
| RTCDyn | Transient user data (for example, endpoints and subscriptions, and transient conferencing state) | Enterprise Edition, back-end database; Standard Edition, Microsoft SQL Server 2005 Express with SP2. |
| RTCab | Database containing global address information used by Address Book Web query service to support Address Book search queries from Communicator Mobile for Windows clients | Enterprise Edition, back-end database; Standard Edition, Microsoft SQL Server 2005 Express with SP2. |
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
…that will produce the following result:
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.
Comments
- Anonymous
October 15, 2010
Hi 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 ? cheers j