List all Active Conferences via PowerShell

UPDATE: 11/20/2013 – 10:24 AM.  Fixed an issue on the script.  Sorry everyone!

UPDATE: 09/12/2018 – Jameson took this script, made it better and posted it to GitHub.

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.

35 thoughts on “List all Active Conferences via PowerShell

  • November 19, 2013 at 10:22 pm
    Permalink

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

    Thanks,
    Golan

    Reply
    • November 19, 2013 at 10:49 pm
      Permalink

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

      Reply
      • September 17, 2014 at 6:01 pm
        Permalink

        I would absolutely LOVE to see the updated SQL code for this for Lync Server 2010!!!

        mG

        Reply
      • January 13, 2015 at 12:03 am
        Permalink

        Did you ever create this script for Lync 2010?

        Reply
    • August 23, 2017 at 8:23 am
      Permalink

      Hi.
      Is there current powershell script for download ?
      Download link above returns:
      Not Found
      The requested URL /wp-content/uploads/sites/2/2013/11/Get-CSActiveConferences.txt was not found on this server.

      thx.

      Reply
  • November 20, 2013 at 9:57 am
    Permalink

    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

    Reply
    • May 20, 2015 at 3:46 pm
      Permalink

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

      Reply
  • November 20, 2013 at 1:40 pm
    Permalink

    Hello,

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

    Reply
  • November 20, 2013 at 4:03 pm
    Permalink

    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]

    Reply
    • November 20, 2013 at 4:20 pm
      Permalink

      Damn you copy/paste monster. Someone I uploaded an older version. Fixed.

      Reply
  • Pingback: Active Conference PowerShell Script

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

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

  • January 15, 2014 at 1:13 am
    Permalink

    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”

    Reply
    • January 20, 2014 at 11:05 pm
      Permalink

      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.

      Reply
      • January 23, 2015 at 2:28 pm
        Permalink

        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.

        Reply
  • June 10, 2014 at 11:01 pm
    Permalink

    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.

    Reply
  • June 12, 2014 at 1:01 pm
    Permalink

    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.

    Reply
  • September 18, 2014 at 3:06 pm
    Permalink

    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?

    Reply
  • Pingback: Lync 2013 – Searching for Future meetings |

  • June 22, 2015 at 5:21 am
    Permalink

    Hi Richard,

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

    Reply
  • August 18, 2015 at 3:41 pm
    Permalink

    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

    Reply
    • October 26, 2015 at 7:35 am
      Permalink

      Hi Richard ,

      Can you please check above query

      Reply
  • October 15, 2015 at 2:03 am
    Permalink

    Any idea if there’s a way to add what modalities are active in the conference? For example, IM-only, voice, screen share, video, etc?

    Reply
  • April 1, 2016 at 9:48 pm
    Permalink

    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”

    Reply
  • November 29, 2016 at 8:32 pm
    Permalink

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

    Reply
  • November 29, 2016 at 10:34 pm
    Permalink

    Awesome script! Thank You!

    Reply
  • March 2, 2017 at 3:53 pm
    Permalink

    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.

    Reply
  • April 21, 2017 at 2:12 pm
    Permalink

    I only seem to get results for myself, not all users meetings, any thoughts?

    Reply
  • September 5, 2017 at 3:01 pm
    Permalink

    It seems download link does not work. I’ll appreciate if you can make it downloadable again. Thanks.

    Reply
  • September 21, 2017 at 12:31 am
    Permalink

    Is the script still available? Link no longer appears to be valid. Thanks!

    Reply
  • April 12, 2018 at 6:46 am
    Permalink

    Hi ,

    Greetings!!!

    I am getting error in power shell while running any cmdlet in lync 2013 edge server. kindly help me to fix the issue

    “Get-CsPool : Verify that Active Directory is prepared for Lync Server.
    At line:1 char:1
    + Get-CsPool
    + ~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Get-CsPool], ADOperationExcep
    tion
    + FullyQualifiedErrorId : Microsoft.Rtc.Management.ADConnect.ADOperationEx
    ception,Microsoft.Rtc.Management.Xds.GetPoolCmdlet

    Reply
  • June 6, 2018 at 7:49 am
    Permalink

    file no longer available…is there a better way to do this now?

    Reply

Leave a Reply to Mark Gutowski Cancel reply

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