Did anyone ever ask you to find how many users are in Lync conference right now ? !!

I was asked to do so ...was almost threatened to be killed if I could not. What comes next is what saved my life.

 

This script lists all the users who are currently in a conference call.

You need to run the script from one of the Front End Servers in the pool it automatically discovers the pool and the associated SQL backend.

(P.S. The account must have Select permission on the rtcdyn database)

 

# Discover the backend SQL server instance  

$ipProperties = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

$machineFQDN = '{0}.{1}' -f $ipProperties.HostName, $ipProperties.DomainName

$computerObject = get-CsComputer $machineFQDN

$poolFQDN = $computerObject.Pool

$userDb = Get-CsService -UserDatabase | where{$_.DependentServiceList -Contains 'UserServer:'+$poolFQDN }

$sqlServerName = $userDb.PoolFqdn

$sqlInstance = $userDb.SqlInstanceName

# Define SQL Command  

$command = New-Object System.Data.SqlClient.SqlCommand $command.CommandText = "select c.ConfId as 'Conference ID', p.UserAtHost, p.JoinTime from [rtcdyn].[dbo].[ActiveConference] c inner join [rtcdyn].[dbo].[Participant] p on c.ConfId = p.ConfId"                      

$connstring = "server="+$sqlServerName+"\"+$sqlInstance+";database=rtc;trusted_connection=true;"

$connection = New-Object System.Data.SqlClient.SqlConnection     

$connection.ConnectionString = $connstring    $connection.Open()   

$command.Connection = $connection   

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter   

$sqladapter.SelectCommand = $command   

$results = New-Object System.Data.Dataset    $recordcount=$sqladapter.Fill($results)    $connection.Close()    $Results.Tables[0]