List all Active Conferences via PowerShell

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.

pic2

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:

pic3

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:

pic1

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.

Share

28 comments on “List all Active Conferences via PowerShell”

  1. Golan Reply

    Hello,
    It works great for Lync server 2013.
    Does it work for Lync server 2010?

    Thanks,
    Golan

    • Richard Brynteson Reply

      It would be different in 2010 because of how conference data was stored in 2010. I’ll post the SQL query for it later.

  2. Nanda Kumar K P Reply

    Hi Richard,
    I am nanda kumar, from bangalore, india.
    As i am new to lync, through googling , youtube video’s, i started to setup Lync 2013 with Windows Server 2012 R2 Standard edition in our organization.
    Beginning installed AD Certificate, AD Service, Powershell, windows features required for lync all things i successfully installed. Till upto Preparing Forest, Schema, Deployment Wizard, Topology all went fine.I have struck @ Install or update lync server System – Install Local Configuration Store – Getting error at the creation of sqlinstance rtclocal. Even i tried by running this manual command ( SQLEXPR_x64.exe /QUIET /IACCEPTSQLSERVERLICENSETERMS /HIDECONSOLE /ACTION=Install /FEATURES=SQLEngine,Tools /INSTANCENAME=RTCLOCAL /TCPENABLED=1 /SQLSVCACCOUNT=”NT AUTHORITYNetworkService” /SQLSYSADMINACCOUNTS=”BuiltinAdministrators” /BROWSERSVCSTARTUPTYPE=”Automatic” /AGTSVCACCOUNT=”NT AUTHORITYNetworkService” /SQLSVCSTARTUPTYPE=Automatic) even though no luck. Could you please help me out on this, am not able to proceed further with lync installation.

    Thank you very much in advance for your reply.

    Regards,
    Nanda Kumar K P

    • karan kumar Reply

      Nanda, please reach out to me over email or share your email and i we can discuss the issue you are seeing.

  3. Ahmed Mostafa Reply

    Hello,

    I tested it, it gave me nothing. I turned off backend sql server firewall.

  4. Harry Reply

    This line needs to be corrected:
    #Append All strResults
    $strResults = $sResults + $Results.Tables[0]
    Can either be
    #Append All strResults
    $strResults = $strResults + $Results.Tables[0]
    or
    #Append All strResults
    $strResults += $Results.Tables[0]

  5. Pingback: Active Conference PowerShell Script

  6. Pingback: NeWay Technologies – Weekly Newsletter #70 – November 21, 2013 | NeWay

  7. Pingback: NeWay Technologies – Weekly Newsletter #70 – November 22, 2013 | NeWay

  8. Marc Reply

    Works great for 2013. I modified to return ExternalConfID as well. This will allow you to recreate the meeting link for active conferences. Is there any way to get the PSTN Dialin Conference ID like you see in Meeting Entry Info?

    $sqlCommand.CommandText = “SELECT rtcdyn.dbo.ActiveConference.ConfId AS ‘Conference ID’, rtcdyn.dbo.ActiveConference.Locked, rtcdyn.dbo.Participant.UserAtHost AS ‘Participant’, DATEADD(HOUR, $iUtcOffset, rtcdyn.dbo.Participant.JoinTime) AS ‘Join Time’, rtcdyn.dbo.Participant.EnterpriseId, rtcdyn.dbo.ActiveConference.IsLargeMeeting AS ‘Large Meeting’, `
    ‘$computer’ as LookupFQDN , c.ExternalConfId as ExternalConfID`
    FROM rtcdyn.dbo.ActiveConference INNER JOIN `
    rtcdyn.dbo.Participant ON rtcdyn.dbo.ActiveConference.ConfId = rtcdyn.dbo.Participant.ConfId `
    JOIN [rtc].[dbo].[Conference] c ON c.ConfId = ActiveConference.ConfId
    JOIN [rtc].[dbo].[PstnMeetingId] p ON p.ExternalConfId = c.ExternalConfId”

    • Richard Brynteson Reply

      It doesn’t appear that this information is directly available in the database but is rather “created” in the background. I have some e-mails out to people to see if anyone knows where the info really is sitting.

      • Rico Reply

        Hi Richard, Did you ever get an update on this? I have a customer who is having issues with “orphaned” PSTN conf ID’s and I’m looking for a way to list all the PSTN conf ID’s.

  9. John Reply

    The data from this query never seems to exactly match data from Get-CsWindowsService (# of active conferences and connected users). I would like to be able to investigate when Stop-CsWindowsService -graceful stalls on connections, but don’t know where to look.

  10. Pat Richard (@patrichard) Reply

    I would change the last line from
    $strResults | ft ‘Participant’, ‘Join Time’, ‘Large Meeting’ -GroupBy ‘Conference ID’
    to just
    $strResults
    as that allows the user to further tweak the output format.

  11. Dave Reply

    Richard…Useful script for showing active conferences within the IM MCU. After verifying against get-csWindowsService across all FE pool servers, I noticed that the related output also shows active conference info for AV MCU and AS MCU. Any chance that you are working on modifying your script to include data from these 2 MCUs?

  12. Pingback: Lync 2013 – Searching for Future meetings |

  13. Roshan Reply

    Hi Richard,

    Works great in Lync2013, very usefull.
    Keep up the good work
    thanks
    roshan

  14. Gaurav Gupta Reply

    Hi Richard ,

    Its a great script and working perfectly fine . However can you tell me what changes need to make if I want to get data for any specific period like for previous month . If you can edit script as its takes input for time period also same as of pool name .

    PS : I have a multi pool architecture and I am bit new to Lync and Powershell

  15. Sean Reply

    Here is a way to list the servers where there are conferencing servers, rather than guessing at which pool you want

    write-host “The Conferencing server pool FQDNs are below”
    get-cspool | where-object {$_.Services -match “Conferenc”} | select-object FQDN
    write-host “”
    $strPoolFQDN = Read-Host -Prompt “Enter the conference server Pool FQDN you want to see the conferences for”

  16. Jesse Sparks Reply

    It would also be handy to have it display the active server that is hosting the conference. How would we do that?

  17. Patrick Reply

    Richard, love the script.
    Just one question:
    On the results some participants show as: CASusername. and then a bunch of numbers.

    What does the CAS….. mean exactly?
    Other users show as just their SIP address and not the CAS…..

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *