Lync 2013 – Searching for Future meetings

A customer wanted to identify users that have future Lync meetings scheduled so that they could target and offer assistance to key users in updating their meeting address as part of a migration to Lync Online, other users would have to request installation of the Lync meeting update tool.

Rather than look to Exchange and performing a discovery request on all mailboxes I looked for a Lync solution, having previously used a script by Richard Brynteson (http://masteringlync.com/2013/11/19/list-all-active-conferences-via-powershell/) that provided all running conferences I used that as a starting point and after a bit of trial and error I settled on the following SQL query to get the information I needed:

“SELECT Conference.ExternalConfId AS ‘ExternalID’, Conference.Static AS ‘Static’, Conference.ConfId AS ‘Conference ID’, Resource.UserAtHost AS ‘MeetingUser’,Conference.ExpiryTime AS ‘Day’ FROM Conference INNER JOIN Resource ON Conference.OrganizerId = Resource.ResourceId”

I had to modify the connection string as we were looking at a different database

$sqlConnString = “server=$ComputerNamertclocal;database=rtc;trusted_connection=true;”

One time Lync meetings with a unique address will expire 14 days after their scheduled start, reoccurring meetings with a defined end date will expire 14 days after the last occurrence, as a result the script will remove those extra days off the date value (it actually removes 15 from the date as its 14 whole days).

As we were only interested in meetings with their own meeting address we needed to filter out the dedicated addresses, fortunately these are identified with a static field with 1 being static and 0 being unique

Since the script queries all frontends in the pool I initially found that I had multiple conferences showing with the same external ID, so I have all duplicates removed.

I found that there were some meetings still in the database that seemed tied to users who no longer exist, so I added a section to lookup the sip address recorded as the creator of each meeting and if it still exists keep the entry in the array, this however adds quite a bit of processing time depending on the number of meetings scheduled so you may wish to remove that check.

This is provided as is and without any support, I hope it helps.

Param (
$strPoolFQDN = (Read-Host -Prompt “Please enter the Pool FQDN”)
)
##### Script Information
#
# Original script written by Richard Brynteson – http://masteringlync.com
#
# Resetting variables to null
$strResults = $null
$TEMPResults= $null
# Setting temp array correctly
$TEMPResults=@()
#Get all the CS pool information
$CSPool = Get-CSPool $strPoolFQDN
#Loop Through Front-End Pool
Foreach ($Computer in $CSPool.Computers){
#Get Computer Name
$ComputerName = $Computer.Split(“.”)[0]
#Connection String
$sqlConnString = “server=$ComputerNamertclocal;database=rtc;trusted_connection=true;”
#SQL Command
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandText = “SELECT Conference.ExternalConfId AS ‘ExternalID’, Conference.Static AS
‘Static’,Conference.ConfId AS ‘Conference ID’, Resource.UserAtHost AS
‘MeetingUser’,Conference.ExpiryTime AS ‘Day’ FROM Conference INNER JOIN Resource ON Conference.OrganizerId =
Resource.ResourceId”
#Connect to SQL
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $sqlConnString
$sqlConnection.Open()
$sqlCommand.Connection = $sqlConnection
#Query Server
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$results = New-Object System.Data.Dataset
$recordcount=$sqladapter.Fill($results)
#Close DB Connection
$sqlConnection.Close()
#Append All strResults
$strResults = $strResults + $Results.Tables[0]
}
# Remove duplicates generated on each frontend
$strResults = $strResults | select -Unique ExternalID,MeetingUser,Static,Day
#Adjust date to account for delay in meeting expirying 14 days after schedule start
foreach ($username in $strResults)
{
try {
$adjusted=($username.Day.AddDays(-15))
$username.Day=$adjusted
}
catch{}
}
# Remove static meetings
#
$count=0
foreach ($username in $strResults)
{
if ($username.static -eq $false)
{
$TEMPresults=$TEMPresults+$username
}
$count=$count+1
}
$strresults=$TEMPresults
#Remove past events
$today=get-date -Format G
$count=0
$TEMPresults=@()
foreach ($username in $strResults)
{
try{
if ($username.day -gt $today)
{
$TEMPresults=$TEMPresults+$strResults[$count]
}
$count=$count+1
}
catch
{
$TEMPresults=$TEMPresults+$strResults[$count]
$count=$count+1
}
}
$strResults=$TEMPresults
#Remove meetings belonging to users who dont exist anymore
$count=0
$TEMPresults=@()
foreach ($username in $strResults)
{
try{
$user=$username.MeetingUser
if (Get-CsUser sip:$user -ErrorAction stop)
{
$TEMPresults=$TEMPresults+$strResults[$count]
$count=$count+1
}
}
catch
{
#Users who do not exist anymore are caught here and not added to the array
#write-host “error”
$count=$count+1
}
}
$strResults=$TEMPresults
# Sample output from the array
$strResults | sort -Property MeetingUser,day | ft ‘MeetingUser’, ‘Day’, ‘ExternalID’

About the author

risual