Useful OCS SQL queries (Part I)

After you get a fully functional OCS environment you want to check some statistics on it's use.
Since all of the info is on the DB it should be easy to get it and even create some reporting (Reporting Services) on top of it.

Next i'll show you some queries i find useful to have at hand.

Client versions

You can check a overall of client versions on your environment using OCS MMC:

But, you might want to get a little more details, as in, who's using what version. You can do that by running this query on the backend.

SELECT     rtc.dbo.Resource.UserAtHost, CAST(rtcdyn.dbo.Endpoint.ClientApp AS varchar(128)) AS ClientApp
FROM         rtcdyn.dbo.Endpoint INNER JOIN
                      rtc.dbo.Resource ON rtcdyn.dbo.Endpoint.OwnerId = rtc.dbo.Resource.ResourceId
order by UserAtHost

This will return a list showing what versions are being used and who's using them. It will even show if the same user is using different devices (as in one user on CWA, MOC, COMO, etc at the same time).

If you need the same query that MMC uses, this is it:

SELECT CAST(ClientApp AS varchar(128)) AS ClientApp, COUNT(*) AS Clients
FROM rtcdyn.dbo.Endpoint
Group by ClientApp

It's not something you will use on a daily base but it's useful to have.