Getting the Office Communicator Client Version by User
A useful feature of OCS admin tools is the ability to determine the client versions that are currently active on the pool. This information typically leads to the question: “How do I find out which user is using client version X?”
This information is stored in the rtcdyn database and can be accessed via a custom SQL query. The client version is stored in the Endpoints table in the rtcdyn database. The column name is ClientApp and has a data type of varbinary. This data type means that the client version is stored in the database like “0x525443432F332E352E302E3020526573706F6E73655F47726F75705F53657276696365”. Fortunately, SQL has a built-in function called Convert that can be used to convert the varbinary data type to a readable format.
The information needs in the Endpoints table needs to be cross referenced with the Users table in the rtc database so that you can find the user’s SIP URI.
Here is the final SQL query that will show you client version for each active user:
Select Convert(varchar(1024),ClientApp) as ClientVersion,
UserAtHost as UserSipUri
FROM rtcdyn.dbo.Endpoint as E,
rtc.dbo.Resource as R
Where E.OwnerId = R.ResourceId
Order by ClientApp
If you happen to have SQL workstation components installed you can use the query editor to run the query editor to run this directly on the database.
If you don’t have the SQL workstation components installed (as you might not on a standard edition server) fear not. I have created a PowerShell script that will execute the query. The results will be shown on the screen and saved to a file ClientVersionsByUser.csv. (If you have a 1000s of clients authenticated you would just see a bunch of scrolling in PowerShell)
Param( [string] $sqlserver = $(Read-Host "Please specify the SQL server/instance:"))
#Define Connecting String
$connstring = "server=$sqlserver;Integrated Security=SSPI;database=rtcdyn"
#Define Query
$query = "Select Convert(varchar(1024),ClientApp) as ClientVersion, UserAtHost as UserSipUri FROM rtcdyn.dbo.Endpoint as E, rtc.dbo.Resource as R Where E.OwnerId = R.ResourceId Order by ClientApp"
#Create Data Adapter and Dataset
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $connString)
$dataset = new-object "System.Data.DataSet" "MyDataSet"
#Fill the dataset with the results from the SQL query
$da.Fill($dataset) | Out-Null
#Close the Data Adapter
$da.Dispose()
#Save and Display Results
$dataset.Tables[0] | Export-Csv -Path "ClientVersionByUser.csv"
$dataset.Tables[0] | ft -AutoSize
Notes and Limitations
- Only users currently logged in to the pool will be reported in the results
- The script must be run locally on a Standard Edition Server
- The user running the script must have permissions to query the database tables. A user in the RTCUnivesalServerAdmins group or local administrator permission on the SQL server will be able to run the script.
--Nick
