Checking Communicator Endpoint Versions On Your OCS Pools

One of the questions that comes up with OCS deployments that have been around for a while is the question of what clients are connecting to the pool. This can be controlled with client version filters and the auto update feature of R2, but more often than not there are some straggling clients out there. The challenge for those without some sort of configuration management tool is identifying what users have those old clients.

Microsoft has been nice enough to provide a handy tool within the OCS 2007 R2 Management Console that checks what kind of endpoints are connected to your Front-End server. If you open the OCS MMC, click the pool object and then click the database tab you'll see a number of reports you can run. One of the more useful ones is the client version summary. Just press Go next to it and you'll see it return a list of endpoints.
clientsummary

You can see from the results we still have quite a mix, and even someone still using a Communicator 2005 client! This is useful in providing an overall picture of what's been used, but the question I immediately hear next is "Who's using that version?" Unfortunately, there's no easy way to tell in the console. You can run a per-user-report which will tell you the endpoints a particular user is signed in with, but that's going to be a tedious effort to chug through a long list of names trying to find the offenders who haven't updated their clients. You can see below what searching for a single user returns.
userreport

In order to answer the question of who's using what we need to run a SQL query against the RTCDyn database. I'll say this is definitely not a recommended/supported task, so be sure you know what you're doing here before you start messing around in SQL Management studio. You have the potential to really hose your OCS installation if you start changing database content. The query we'll run is just a SELECT statement so we shouldn't cause any problems. Still, you've been warned.

Open SQL Management Studio. If you have a standard edition pool you can download and install SQL Management Studio Express for free. Press the New Query button and paste in the following query. Then just press the Execute button. You'll get a list back of SIP URIs along with the endpoint they are currently using.

SELECT CAST([SipHeaderFrom] as varchar) as "SIP URI"
      ,CAST([ClientApp] AS varchar) as "Endpoint"
FROM [rtcdyn].[dbo].[Endpoint]

That will give us a nice long of everything in use and what SIP URI is signed in with that client.
sql1

Say we want to filter because we're looking for people with a specific version. In this case, we want to find everyone still using the R1 MOC client so we can add a WHERE clause that searches for strings that match the agent header.

SELECT CAST([SipHeaderFrom] as varchar) as "SIP URI"
      ,CAST([ClientApp] AS varchar) as "Endpoint"
FROM [rtcdyn].[dbo].[Endpoint]
WHERE CAST([ClientApp] as varchar) like '%2.0%'

You could replace that 2.0 with anything else returned in the agent headers such as 3.5, OC, LCC, etc. This only queries the clients that are connected at a specific point in time so you may want to run this from time to time to catch clients that may not have been connected the first time you Hope this helps you identify your clients.