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.

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:

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.

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.

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 ?