# Copyright (c) 2011 Michele Baldessari # # sync-lync-rosters.ps1 is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program. If not, see . # Global Variables $root= New-Object System.DirectoryServices.DirectoryEntry("LDAP://RootDSE") $RosterGroupPath = "C:\service\rosters" $server = "lync.foo.local" $db = "rtc" $conn = "server=$server;integrated security=SSPI;database=$db" $backuppath = "C:\temp\" $domain = "FOO" $realm = "foo.lcl" # Given a string with groupnumbers and a groupnumber it adds it to the string and sorts it Function AddGroupString() { param($groups, $group) $glist = $groups.split(" ") if ($glist -notcontains $group) { $glist += $group } $tmp = $glist | Sort-Object return $tmp -join " " } # Given a string with groupnumbers and a groupnumber it adds it to the string and sorts it Function DeleteGroupString() { param($groups, $group) $glist = $groups.split(" ") if ($glist -contains $group) { $glist = $glist -ne $group } $tmp = $glist | Sort-Object return $tmp -join " " } Function GetOwnerId() { param($owner) $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() $sqlCommand = $sqlConnection.CreateCommand() $sqlCommand.CommandText = "select r.ResourceId from dbo.Resource as r ` inner join dbo.PresenceHomedResource as h on h.ResourceId = r.ResourceId ` where r.UserAtHost = '" + $owner + "'" $sqlReader = $sqlCommand.ExecuteReader() $counter = 0 $ret = "" while ($sqlReader.Read()) { $ret = $sqlReader["ResourceId"] $counter++ } $SqlConnection.Close() if ($counter -gt 1) { throw "More than one OwnerID for $owner" } if ($counter -eq 0) { throw "No OwnerID for $owner" } return $ret } Function GetGroupNumber() { param($ownerid, $groupname) $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() $sqlCommand = $sqlConnection.CreateCommand() $sqlCommand.CommandText = "Select GroupNumber FROM dbo.ContactGroup ` where OwnerId = $ownerid and DisplayName = '" + $groupname +"'" $sqlReader = $sqlCommand.ExecuteReader() $counter = 0 $ret = "" while ($sqlReader.Read()) { $ret = $sqlReader["GroupNumber"] $counter++ } $SqlConnection.Close() if ($counter -gt 1) { throw "More than one GroupNumber for $owner - $groupname" } if ($counter -eq 0) { throw "No GroupNumber for $groupname" } return $ret } Function GetContacts() { param($owner) $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() $sqlCommand = new-object System.Data.SqlClient.SqlCommand("UserMgmtGetContacts", $sqlConnection) $sqlCommand.CommandTimeout = 120 $sqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure' $sqlCommand.Parameters.Add("@_Owner", $owner) | out-Null $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() $buddies = @{} foreach ($Row in $DataSet.Tables[1].Rows) { $groups = $($Row[3]) #.split(" ") $owner = $($Row[0]) try { $ownerid = GetOwnerId $owner # Value is an Array with owner, displayname, External Uri, Groups, Subscribed $a = $owner, (new-Object System.Text.asciiEncoding).GetString($Row[1]), (new-Object System.Text.asciiEncoding).GetString($Row[2]), $groups, $($Row[4]) $buddies.Add($ownerid, $a) } catch { } } return $buddies } Function CreateGroup() { param($owner, $groupname) $enc = new-Object System.Text.asciiEncoding $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() $sqlCommand = new-object System.Data.SqlClient.SqlCommand("UserMgmtAddGroup", $sqlConnection) $sqlCommand.CommandTimeout = 120 $sqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure' $sqlCommand.Parameters.Add("@_Owner", $owner) | Out-Null $sqlCommand.Parameters.Add("@_DisplayName", $enc.GetBytes($groupname)) | Out-Null [Byte[]] $y = @() $sqlCommand.Parameters.Add("@_ExternalUri", [System.Data.SqlDbType]"VarBinary") | Out-Null $sqlCommand.Parameters["@_ExternalUri"].Size = 0 $sqlCommand.Parameters["@_ExternalUri"].Value = $y $sqlCommand.Parameters.Add("@_GroupLimit", 60) | Out-Null $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() } Function DeleteGroup() { param($owner, $groupname) $groupnumber = GetGroupNumber (GetOwnerId $owner) $groupname Write-Host "Deleting group $groupname and $owner" $enc = new-Object System.Text.asciiEncoding $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() # First we launch a setcontact of all the buddies that are in this group, then # we can eliminate the group itself otherwise the stored procedure will error out on references $buddies = GetContacts $owner foreach ($i in $buddies.keys) { $buddyid = $i $buddyname = $buddies[$i][0] $newgroups = DeleteGroupString $buddies[$i][3] $groupnumber #Write-Host "Groups before $groups and groups after " $newgroups #Write-Host "SetContact $owner " $buddies[$i][0] " - " $buddies[$i][1] " SubPresence : 1 - Groups : " $newgroups " Ext URI : " $buddies[$i][2] $sqlCommand = new-object System.Data.SqlClient.SqlCommand("UserMgmtSetContact", $sqlConnection) $sqlCommand.CommandTimeout = 120 $sqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure' $sqlCommand.Parameters.Add("@_Owner", $owner) | Out-Null $sqlCommand.Parameters.Add("@_Buddy", $buddies[$i][0]) | Out-Null $sqlCommand.Parameters.Add("@_DisplayName", $enc.GetBytes($buddies[$i][1])) | Out-Null $sqlCommand.Parameters.Add("@_SubscribePresence", 1) | Out-Null $sqlCommand.Parameters.Add("@_Groups", $newgroups) | Out-Null $sqlCommand.Parameters.Add("@_ExternalUri", $enc.GetBytes($buddies[$i][2])) | Out-Null $sqlCommand.Parameters.Add("@_BuddyLimit", 60) | Out-Null $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() } $sqlCommand = new-object System.Data.SqlClient.SqlCommand("UserMgmtDeleteGroup", $sqlConnection) $sqlCommand.CommandTimeout = 120 $sqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure' $sqlCommand.Parameters.Add("@_Owner", $owner) | Out-Null $sqlCommand.Parameters.Add("@_GroupNumber", $groupnumber) | Out-Null $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() } Function FillGroup() { param($owner, $groupname, $buddylist) $enc = new-Object System.Text.asciiEncoding $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $conn $sqlConnection.Open() $buddyids = @{} foreach ($i in $buddylist) { $id = GetOwnerId $i $buddyids.Add($id, $i) } $ownerid = GetOwnerId $owner $contacts = GetContacts $owner $groupnumber = GetGroupNumber $ownerid $groupname foreach($buddy in $buddyids.Keys) { if ($buddy -eq $ownerid) { continue } $sqlCommand = new-object System.Data.SqlClient.SqlCommand("UserMgmtSetContact", $sqlConnection) $sqlCommand.CommandTimeout = 120 $sqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure' # if $buddy does not exist as a key in $contacts then we need to add that contact first if ($contacts.ContainsKey($buddy)) { $groups = $contacts[$buddy][3] $newgroups = AddGroupString $groups $groupnumber Write-Host "SetContact $owner " $contacts[$buddy][0] " - " $contacts[$buddy][1] " SubPresence : 1 - Groups : " $newgroups " Ext URI : " $contacts[$buddy][2] $sqlCommand.Parameters.Add("@_Owner", $owner) | Out-Null $sqlCommand.Parameters.Add("@_Buddy", $contacts[$buddy][0]) | Out-Null $sqlCommand.Parameters.Add("@_DisplayName", $enc.GetBytes($contacts[$buddy][1])) | Out-Null $sqlCommand.Parameters.Add("@_SubscribePresence", 1) | Out-Null $sqlCommand.Parameters.Add("@_Groups", $newgroups) | Out-Null $sqlCommand.Parameters.Add("@_ExternalUri", $enc.GetBytes($contacts[$buddy][2])) | Out-Null $sqlCommand.Parameters.Add("@_BuddyLimit", 120) | Out-Null } else { # This contact does not exist in the roster yet, so we put him with groups set to this new group only $newgroups = AddGroupString "" $groupnumber $displayname = "" [Byte[]] $exturi = @() Write-Host "SetContact* $owner " $buddyids[$buddy] "-" $displayname "- SubPresence : 1 - Groups :" $newgroups " - Ext URI : " $exturi $sqlCommand.Parameters.Add("@_Owner", $owner) | Out-Null $sqlCommand.Parameters.Add("@_Buddy", $buddyids[$buddy]) | Out-Null $sqlCommand.Parameters.Add("@_DisplayName", $enc.GetBytes($displayname)) | Out-Null $sqlCommand.Parameters.Add("@_SubscribePresence", 1) | Out-Null $sqlCommand.Parameters.Add("@_Groups", $newgroups) | Out-Null $sqlCommand.Parameters.Add("@_BuddyLimit", 120) | Out-Null $sqlCommand.Parameters.Add("@_ExternalUri", [System.Data.SqlDbType]"VarBinary") | Out-Null $sqlCommand.Parameters["@_ExternalUri"].Size = 0 $sqlCommand.Parameters["@_ExternalUri"].Value = $exturi } $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() } Write-Host "" } Function BackupDatabase() { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null $today = Get-Date $backupfile = $server + "-" + $db + "-" + $today.Year + "-" + $today.Month + "-" +$today.Day + "-" + $today.hour + ".bak" $backupfullpath = $backuppath + $backupfile Write-Host "Full backup of $server on db $db to: $backupfullpath" $dbserver = New-Object ("Microsoft.SqlServer.Management.Smo.Server") ($server) $dbBackup = new-Object ("Microsoft.SqlServer.Management.Smo.Backup") $dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore") $dbBackup.Database = $db $dbBackup.Devices.AddDevice($backupfullpath, "File") $dbBackup.Action="Database" $dbBackup.Initialize = $TRUE $dbBackup.SqlBackup($dbserver) if(!(Test-Path $backupfullpath)) { Write-Host "Backup to $backupfullpath did not work as expected" Exit } } Function GetUsersFromRosterFile() { param($rosterfile) $tmp = Get-Content $rosterfile | sort $users = @() foreach ($t in $tmp) { $users += $t.Trim() } # Check that the samAccountName is correct and that it is a SIP/Lync active user foreach ($samaccountname in $users) { # Checking that the user exists in AD $filter = "(&(objectCategory=Person)(objectClass=User)(samAccountName=$samaccountname))" $searcher = New-Object System.DirectoryServices.DirectorySearcher $filter $foundusers = $searcher.findall() $count = 0 if ($samaccountname.Length -lt 2) { Continue } # Skip lines less than two chars long foreach ($found in $foundusers) { $count = $count + 1 # If user is found in AD also check it is a Lync user $foundinlync = Get-CsUser -Identity "$domain\$samaccountname" -ErrorAction SilentlyContinue if (!$foundinlync) { Write-Host -foregroundcolor red "Lync is not enabled for $domain\$samaccountname. Please fix this first" Exit } } if ($count -eq 0) { Throw "$samaccountname in $rosterfile not found stopping!!" Exit } } $newusers = @() foreach ($t in $users) { $newusers += $t + "@" + $realm } return $newusers } BackupDatabase $files = dir $RosterGroupPath | Where { $_.Extension -match "csv" } | sort name $contactgroups = @{} Write-Host "The following rosters have been found:" Write-Host "" foreach ($f in $files) { $contacts = GetUsersFromRosterFile $f.FullName $contactgroups.Add($f.BaseName, $contacts) Write-Host -nonewline -foregroundcolor magenta $f.BaseName Write-Host ":" $contacts } Write-Host "" Write-Host "All contacts have been found in AD and are Lync-enabled. No errors found" $answer = Read-Host "If you want ALL groups to be set press Enter, otherwise type only the groups you want (use space to separate them)" if ($answer -eq "") { Write-Host "Setting all the contact groups" $dogroups = $contactgroups.keys } else { Write-Host "Setting only the following contact groups: $answer" $dogroups = $answer.Split(" ") } foreach ($group in $contactgroups.Keys) { if ($dogroups -notcontains $group) { Write-Host -nonewline "Skipping " Write-Host -foregroundcolor magenta $group Continue } Write-Host -nonewline "Working on " Write-Host -nonewline -foregroundcolor magenta $group Write-Host ":" $contactgroups[$group] foreach ($i in $contactgroups[$group]) { try { DeleteGroup $i $group } catch {} CreateGroup $i $group FillGroup $i $group $contactgroups[$group] } } Write-Host -foregroundcolor yellow "Done. Check that everything is OK. In case of major problem restore from backup"