How to find out the SIP URIs of users connected on one particular OCS Front End server

There might be situation that you would like to know the SIP URIs of the users, which are connected to one of the OCS servers under a pool.

This article is to understand a SQL query which will help us find out list of users, which are connected to one particular OCS Front End Server.

You can even run this query on a standard edition OCS server to check, how many users are connected to the OCS Server at that point of time.

Select a.UserAtHost "Active-Users" from rtc.dbo.Resource

a,rtcdyn.dbo.DeliveryContext b where a.ResourceId=b.SubscriberId and b.FrontEndId=1

In above query FrontEndID is the corresponding ID of the OCS Front End server where the users are connected to.

This ID can be obtained from the FrontEnd table of the rtcdyn database.

This table has OCS FE FQDN and corresponding FrontEndId.

To run the above query on the Enterprise Edition OCS setup.

a) In order to run the above SQL query, go to the backend SQL server

b) Open Microsoft SQL Server Management Studio

c) Click "New Query"

d) Paste the above query and click "Execute" button. It will show the list fo users which are connected to the Front End server at the moment.

To run the above query on the Standard Edition OCS setup.

a) Download and Install "SQL Server Managment Studio Express Edition"

b) Open SQL Server Management Studio Express Console.

c) Connect to <ServerName>\RTC using Windows Authentication

d) Click "New Query"

e) Paste the above query and click "Execute" button. It will show the list fo users which are connected to the Front End server at the moment.

Comments

Anonymous
January 01, 2003
Ok... I have one clue what you can use it to play with the available script to get rid of your problem.. If you see the table DeliveryContext under the database rtcdyn, its giving you two subscriber ids for each user.. n thats what is making this script produce two times SIP URI. Just put something in the script to ensure subscriberId remains unique.
Anonymous
January 01, 2003
Thanks for the SQL query. I was searching a way to find out active users for a long time. When I run this query the result lists SIP uri's of a user twice. Can you please let me know what has to be changed or am I doing something wrong.
Anonymous
January 01, 2003
Well.. I could play with the SQL scripts after a long time.. and I dont know for what reason even I am getting the same result. That is duplicate SIP URIs... I might try to explore the way this script is working but its not possible for next few days.. due to some important assignments I have.
Anonymous
January 05, 2015
Select distinct a.UserAtHost "Active-Users" from rtc.dbo.Resource
a,rtcdyn.dbo.DeliveryContext b where a.ResourceId=b.SubscriberId and b.FrontEndId=1