This posting is provided "AS IS" with no warranties, and confers no rights.The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway. Please use the Microsoft Forums for support requests.
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.
Recently I had to troubleshoot a strange case where a recently OCS Archiving + Monitoring server (collocation of these 2 roles is supported) was not collecting any data. I was monitoring the data through the use of this neat Resource Kit tool:
ArchivingCDR Reporter (ArchivingCDRReporter.exe, ArchivingCDRReporter_Config.xml) This reporting tool has built-in SQL queries to retrieve and view information from the Archiving and Call Detail Records (CDR) Backend. The tool enables the user to view Office Communications Server 2007 usage reports based on the Archiving and CDR tables.
Everything seemed to be pretty well configured, Archiving and Monitoring were enabled both at the forest level and pool level, as explained in Deploying Monitoring Server and Deploying Archiving Server.
The Operating System was Windows Server 2008 R2, but since all the steps from KB982021 had been followed, that version of OS is fully supported.
So, I had to stop for a while and think… Looking at the archiving and monitoring architecture in Office Communications Server 2007 R2 may help a little bit:
Since no data was being delivered to the DB server and the connection between the OCS Archiving + Monitoring server was OK, maybe the problem had to do with the Messaging Queuing (MSMQ) service.
In fact, the Application event log of the server had a couple of error events related with MSMQ:
The Message Queuing service rejects incoming messages when it is unable to check whether the sender is allowed access to the queue for sending messages. In this case, the queue affected is OCS-ARCH-MON\private$\lcslog, but note that an event might not be issued every time this problem occurs. To perform this access check, Message Queuing needs access to the TokenGroupsGlobalAndUniversal attribute of the sender's user object. Only users with domain administration permissions can add members to the Windows Authorization Access Group, which is allowed access to the TokenGroupsGlobalAndUniversal attribute, in one of two ways: 1) For best security practice, add only the computer accounts that need access to the TokenGroupsGlobalAndUniversal attribute to the Windows Authorization Access Group. The domain administrator will repeat this operation for other Message Queuing computers that require the permission, manually adding the relevant accounts to the Windows Authorization Access Group. 2) As a less secure practice, add the Authenticated Users group to the Windows Authorization Access group. This grants every authenticated user, including the Message Queuing service on any computer, access to the TokenGroupsGlobalAndUniversal attribute for all users, and requires no further manual administration.
I followed the suggested recommendation of adding the MSMQ computer accounts (OCS Frontend, OCS Archiving + Monitoring) to the Windows Authorization Access Group (located in the Builtin container).
After that, in order to correct the problem, you must restart the MSMQ service on the Archiving + Monitoring server (this will also restart the OCS services). On the OCS Frontend you must also restart the MSMQ service, the QoE Agent Service and also the Frontend service. Restarting both servers will also do the trick.