The following SQL query will get scheduled conferences from the Lync RTC database. From a Lync Standard Edition server its in the RTCLOCAL instance, otherwise you’ll find it in the backend SQL server. To run the query you will need to open SQL Server Management Console, connect to the database, then open a new Query. Copy the following code snippet and run.
All Users
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE RTC --SELECT * FROM Resource --SELECT * FROM Conference SELECT ConfId, CASE WHEN OrganizerId IS NOT NULL THEN (SELECT TOP 1 UserAtHost FROM Resource WHERE OrganizerId LIKE ResourceId) END AS 'UserSIPAddress', Title, ProvisionTime, LastUpdateTime, ExpiryTime, LastActivateTime FROM Conference |
Specified User
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE RTC SELECT ConfId, CASE WHEN OrganizerId IS NOT NULL THEN (SELECT TOP 1 UserAtHost FROM Resource WHERE OrganizerId LIKE ResourceId) END AS 'UserSIPAddress', Title, ProvisionTime, LastUpdateTime, ExpiryTime, LastActivateTime FROM Conference WHERE (SELECT UserAtHost FROM Resource WHERE OrganizerId LIKE ResourceId) LIKE ('[email protected]') |
Thanks Andrew,
it is very useful.
How can I specific sip address to search the specific user conference information?
Hey John, I had a quick go at this and think I have a working solution. I have added this to the blog post. Let me know how it goes!
Hi, How can I get IP addresses of SfB Client Connected? So we can check from where the Request is coming.
Hey, not sure how you would do that unfortunately. I think this info is held elsewhere in SQL, but unlikely relatable to meetings.
Andrew,
First, thank you for this query. I think this is what I was looking for as I needed to pull a list of conferences out of my 2 pools. Next issue is figuring out which are “active” or not and maybe find a way to clear out the inactive/outdated ones from the database. I’m hoping this data can help.
This query returned a LOT of conferences (user base of approx. 20k users), but is there a way I can tell from this data if the meeting is still active or not? IE, overall it returned 13366 lines. Of those, 13312 have NULL in ExpiryTime and 9936 have NULL in LastActivateTime.
Thank you for your help.. and this query.
Hi Mike. In the past I have used Excel filters to filter to non-expired meetings, then ordered by last active. This appears give you re-occurring and still active meetings + meetings that have not occurred yet (never been active). Hope that helps a little. The report is certainly not perfect.
The report gave me a LOT of information, and for that I’m grateful. Now to figure out how to remove the “NULL” meetings
Hi Adrew, thank you for nice query.
I also need personal note in skype for business all users.
How to get personal note value using query?
Please guide some query.
That’s Top of Skype for business Client Area.
(ex. What’s happening today?)
Thanks and no problem. Not sure how to get the personal note sorry. Not something I have done and I no longer have access to a SfB lab.