Lync 2013 – Searching for Future meetings

2017-12-07T14:31:09+00:00 March 5th, 2015|Skype|

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.

  1. Param (
  2. $strPoolFQDN = (Read-Host -Prompt “Please enter the Pool FQDN”)
  3. )
  4. ##### Script Information
  5. #
  6. # Original script written by Richard Brynteson – http://masteringlync.com
  7. #
  8. # Resetting variables to null
  9. $strResults = $null
  10. $TEMPResults= $null
  11. # Setting temp array correctly
  12. $TEMPResults=@()
  13. #Get all the CS pool information
  14. $CSPool = Get-CSPool $strPoolFQDN
  15. #Loop Through Front-End Pool
  16. Foreach ($Computer in $CSPool.Computers){
  17. #Get Computer Name
  18. $ComputerName = $Computer.Split(“.”)[0]
  19. #Connection String
  20. $sqlConnString = “server=$ComputerNamertclocal;database=rtc;trusted_connection=true;”
  21. #SQL Command
  22. $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
  23. $sqlCommand.CommandText = “SELECT Conference.ExternalConfId AS ‘ExternalID’, Conference.Static AS
  24. ‘Static’,Conference.ConfId AS ‘Conference ID’, Resource.UserAtHost AS
  25. ‘MeetingUser’,Conference.ExpiryTime AS ‘Day’ FROM Conference INNER JOIN Resource ON Conference.OrganizerId =
  26. Resource.ResourceId”
  27. #Connect to SQL
  28. $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
  29. $sqlConnection.ConnectionString = $sqlConnString
  30. $sqlConnection.Open()
  31. $sqlCommand.Connection = $sqlConnection
  32. #Query Server
  33. $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  34. $sqlAdapter.SelectCommand = $sqlCommand
  35. $results = New-Object System.Data.Dataset
  36. $recordcount=$sqladapter.Fill($results)
  37. #Close DB Connection
  38. $sqlConnection.Close()
  39. #Append All strResults
  40. $strResults = $strResults + $Results.Tables[0]
  41. }
  42. # Remove duplicates generated on each frontend
  43. $strResults = $strResults | select -Unique ExternalID,MeetingUser,Static,Day
  44. #Adjust date to account for delay in meeting expirying 14 days after schedule start
  45. foreach ($username in $strResults)
  46. {
  47. try {
  48. $adjusted=($username.Day.AddDays(-15))
  49. $username.Day=$adjusted
  50. }
  51. catch{}
  52. }
  53. # Remove static meetings
  54. #
  55. $count=0
  56. foreach ($username in $strResults)
  57. {
  58. if ($username.static -eq $false)
  59. {
  60. $TEMPresults=$TEMPresults+$username
  61. }
  62. $count=$count+1
  63. }
  64. $strresults=$TEMPresults
  65. #Remove past events
  66. $today=get-date -Format G
  67. $count=0
  68. $TEMPresults=@()
  69. foreach ($username in $strResults)
  70. {
  71. try{
  72. if ($username.day -gt $today)
  73. {
  74. $TEMPresults=$TEMPresults+$strResults[$count]
  75. }
  76. $count=$count+1
  77. }
  78. catch
  79. {
  80. $TEMPresults=$TEMPresults+$strResults[$count]
  81. $count=$count+1
  82. }
  83. }
  84. $strResults=$TEMPresults
  85. #Remove meetings belonging to users who dont exist anymore
  86. $count=0
  87. $TEMPresults=@()
  88. foreach ($username in $strResults)
  89. {
  90. try{
  91. $user=$username.MeetingUser
  92. if (Get-CsUser sip:$user -ErrorAction stop)
  93. {
  94. $TEMPresults=$TEMPresults+$strResults[$count]
  95. $count=$count+1
  96. }
  97. }
  98. catch
  99. {
  100. #Users who do not exist anymore are caught here and not added to the array
  101. #write-host “error”
  102. $count=$count+1
  103. }
  104. }
  105. $strResults=$TEMPresults
  106. # Sample output from the array
  107. $strResults | sort -Property MeetingUser,day | ft ‘MeetingUser’, ‘Day’, ‘ExternalID’