UPDATE: 11/20/2013 – 10:24 AM. Fixed an issue on the script. Sorry everyone!
Let me start by saying that I am NOT even half as good as the great Mr. Pat Richard when it comes to PowerShell. If he is the major league of PowerShell, on my best day I’m a solid backup player for a little league team. Therefore the PowerShell that I’m about to share could be a lot better but think of it as a first step to something better in the future. If you have any improvements feel free to add them to the comments.
Over the last few weeks I’ve been thinking about a way to assist a client in the draining of their servers for the purposes of patching. As it is well documented, the draining process will leave services up and running in a paused state until all active conversations are completed. The issue I’ve ran into a few times however is that I’ve waited over 24 hours and some services (typically the IM MCU) are still up and running. Often times, this is just a random person who left a conference IM window open on their computer but none the less I don’t want to bounce the box if there is a legitimate conference going on.
So I started digging around the database (because that is where all the cool stuff happens in Lync) and knowing exactly where all of the active conference are happening decided to start looking for a way to display that information via PowerShell. I decided the simplest way was to prompt for a pool, look-up the members of that pool and loop through each of the front-end servers.
The SQL is the easy part:
SELECT ActiveConference.ConfId AS ‘Conference ID’, ActiveConference.Locked, Participant.UserAtHost AS ‘Participant’, Participant.JoinTime, Participant.EnterpriseId, ActiveConference.IsLargeMeeting AS ‘Large Meeting’ FROM ActiveConference INNER JOIN Participant ON ActiveConference.ConfId = Participant.ConfId
Each front-end server in the RTCDYN database has an ActiveConference table which contains all of the active conferences running on that particular server.
And the Participants table contains all of the active participants for the call with a corresponding conference ID. Therefore all we need to do is join the two tables together and we have a complete list. Using this information I went ahead and created this short script:
Download Here: Get-CSActiveConferences
You will need to rename the script to .ps1 from .txt. Once you have renamed the file, go ahead and run .Get-CsActiveConferences from the Lync Management Shell. It will prompt you for the name of the pool you want get results from. Your results will look like:
NOTE: You may need to adjust firewall rules if you don’t allow your local SQL databases to be accessible remotely. Port 1433 is the default SQL port.
All meetings will be grouped by Conference ID. By default I’m showing the Participant, Join Time and if it is a Large Meeting. You can always edit the PowerShell file to display other information if needed. If you have any good requests or changes please just pass them along and I’ll post them.