There might be a situation where you would like an overview of the SIP URIs of the active users connected to one of the OCS servers in a pool, their last logon and what client version they are using.
All these kind of information can be retrieved using SQL queries in the SQL Management Tools, connect to the RTC database on the backend server and run the selected queries below.
Get a list of active users on the OCS Server (pool):
Select a.UserAtHost “Active-Users” from rtc.dbo.Resource
a,rtcdyn.dbo.DeliveryContext b where a.ResourceId=b.SubscriberId and b.FrontEndId=1
The FrontEndId is the ID of the OCS Frontend server where the users are connected to. In an environment with only one OCS, the value is 1.
The ID can be obtained from the FrontEnd table of the rtcdyn database.
Further more if you need the above information along with information about which Client App (communicator version) they use, the following query command will give you that result:
select count(*) as Occurrences, cast(e.ClientApp as varchar(128)) as
ClientApp from rtcdyn.dbo.Endpoint as e group by cast(e.ClientApp as
varchar(128)) order by cast(e.ClientApp as varchar(128)) select r.UserAtHost,
cast(e.ClientApp as varchar(128)) as ClientApp from rtcdyn.dbo.Endpoint as e
inner join rtc.dbo.Resource as r on r.ResourceId = e.OwnerId
Need to known when your enabled OCS users last logged in, run the following query below:
select res.UserAtHost as “SIP Address”, hud.LastNewRegisterTime as “Last Logon” from rtcdyn.dbo.HomedUserDynamic hud join
(Select ResourceId, UserAtHost from rtc.dbo.Resource
group by ResourceId, UserAtHost)
res
on hud.OwnerId=res.ResourceId
order by “Last Logon”
All SQL queries work for both OCS Standard and Enteprise Edition.
