Public/Add-UsersToRMGroup.ps1
Function Add-UsersToRMGroup { <# .SYNOPSIS Script uses API calls to move unassigned users to specific groups. .DESCRIPTION Using the API PUT command, the script obtains unassigned users and moves them to a group. The users are found using the Real Migrator Global Master Tracker, then compares against unassigned users for a match. See full instructions in Global Rollout / Documents / PST Migrations / Scripts / Add-UsersToRMGroup .INPUTS To connect to RealMigrator API, you need to be a Super Administrator for that instance of RealMigrator (NAM,HO,EU, etc.). Once a Super Administrator, copy the token from RealMigrator that you will paste into the GUI. Then select what instance to view (North America, Europe, etc). Enter a location code to search for users by. Finally, select what group you would like to move the unassigned users to. .OUTPUTS No outputs .NOTES Author: Jesse Newell Email: jesse.newell@dbschenker.com Last Edit: 2019-09-23 Version 1.1 #> # Load .Net assemblies [void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") [System.Windows.Forms.Application]::EnableVisualStyles() # Form class $Form = New-Object system.Windows.Forms.Form $Form.ClientSize = "610,600" $Form.TopMost = $false $Form.Text = "RealMigrator Report" $Form.StartPosition = "CenterScreen" $Font = New-Object System.Drawing.Font("Times New Roman", 11) $Form.Font = $Font #label for instance selection $labelPrefix = New-Object System.Windows.Forms.Label $labelPrefix.Location = New-Object System.Drawing.Point(330, 140) $labelPrefix.Size = New-Object System.Drawing.Size(240, 40) $labelPrefix.Text = "Select the group to add the unassigned users to:" $Form.Controls.Add($labelPrefix) #Authorization label class $label = New-Object System.Windows.Forms.Label $label.Location = New-Object System.Drawing.Point(10, 20) $label.Size = New-Object System.Drawing.Size(300, 20) $label.Text = "1. Paste your authorization token below:" $Form.Controls.Add($label) #Text box for pasting autorization token. $TextBox = New-Object system.Windows.Forms.TextBox $TextBox.multiline = $true $TextBox.width = 275 $TextBox.height = 70 $TextBox.Location = New-Object System.Drawing.Point(10, 40) $TextBox.Font = 'Microsoft Sans Serif,10' $Form.Controls.Add($textBox) # List populating the list information $listBoxClub = New-Object System.Windows.Forms.ComboBox $listBoxClub.Location = New-Object System.Drawing.Size(330, 180) $listBoxClub.Size = New-Object System.Drawing.Size(270, 50) $listBoxClub.DropDownHeight = 400 $form.Controls.Add($listBoxClub) #Label for selecting the Real Migrator instance $labelComboBox = New-Object System.Windows.Forms.Label $labelComboBox.Location = New-Object System.Drawing.Point(330, 20) $labelComboBox.Size = New-Object System.Drawing.Size(280, 20) $labelComboBox.Text = '2. Select a RealMigrator instance:' $form.Controls.Add($labelComboBox) # Label for list view $labelListView = New-Object System.Windows.Forms.Label $labelListView.Location = New-Object System.Drawing.Point(10, 160) $labelListView.Size = New-Object System.Drawing.Size(280, 20) $labelListView.Text = 'Unassigned users:' $form.Controls.Add($labelListView) # Add a listview. $script:listview = New-Object System.Windows.Forms.ListView $listview.Location = New-Object System.Drawing.Size(10, 180) $listview.Size = New-Object System.Drawing.Size(275, 330) $listview.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right -bor [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Left $listview.View = "Details" $listview.FullRowSelect = $true $listview.multiselect = $true $listview.AllowColumnReorder = $true $listview.GridLines = $true $Form.Controls.Add($listview) $button_refresh = New-Object System.Windows.Forms.Button $button_refresh.Location = New-Object System.Drawing.Size(320, 540) $button_refresh.Size = New-Object System.Drawing.Size(150, 32) $button_refresh.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right $button_refresh.TextAlign = "MiddleCenter" $button_refresh.Text = "Search Again" $button_refresh.Enabled = $False $button_refresh.Add_click( { Get-InputLocationForm }) $form.controls.add($button_refresh) $button_execute = New-Object System.Windows.Forms.Button $button_execute.Location = New-Object System.Drawing.Size(130, 540) $button_execute.Size = New-Object System.Drawing.Size(150, 32) $button_execute.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right $button_execute.TextAlign = "MiddleCenter" $button_execute.Text = "Assign to Group" $button_execute.Enabled = $false $button_execute.Add_click( { MessageBox }) $form.controls.add($button_execute) $arrayinstance = @("Head Office", "Germany", "Europe", "North America", "South America", "Asia Pacific", "China") $comboboxinstance = New-Object System.Windows.Forms.ListBox $comboboxinstance.Location = New-Object System.Drawing.Size(330, 40) $comboboxinstance.Size = New-Object System.Drawing.Size(270, 20) $comboboxinstance.Height = 80 $Form.Controls.Add($comboboxinstance) foreach ($item in $arrayinstance) { [void]$comboboxinstance.Items.Add($item) } $comboboxinstance_SelectedIndexChanged = { #Change the selected instance to something the API GET can understand. if ($comboboxinstance.Text -eq 'Head Office') { $script:instance = 'ho' $Script:Source = "https://dbschenker.sharepoint.com/sites/GlobalWorkplaceManagement/GlobalRollout/Shared%20Documents/PST%20Migrations/Country%20Rollout/HO(DE7499)%20Regional%20Tracker.xlsx?web=1" } if ($comboboxinstance.Text -eq 'Germany') { $script:instance = 'de' } if ($comboboxinstance.Text -eq 'Europe') { $script:instance = 'eu' $Script:Source = "https://dbschenker.sharepoint.com/sites/GlobalWorkplaceManagement/GlobalRollout/Shared%20Documents/PST%20Migrations/Country%20Rollout/EMEA%20Regional%20Tracker.xlsx?web=1" } if ($comboboxinstance.Text -eq 'North America') { $script:instance = 'us' $Script:Source = "https://dbschenker.sharepoint.com/sites/GlobalWorkplaceManagement/GlobalRollout/Shared%20Documents/PST%20Migrations/Country%20Rollout/AMER%20Regional%20Tracker.xlsx?web=1" } if ($comboboxinstance.Text -eq 'South America') { $script:instance = 'us' $Script:Source = "https://dbschenker.sharepoint.com/sites/GlobalWorkplaceManagement/GlobalRollout/Shared%20Documents/PST%20Migrations/Country%20Rollout/AMER%20Regional%20Tracker.xlsx?web=1" } if ($comboboxinstance.Text -eq 'Asia Pacific') { $script:instance = 'apac' $Script:Source = "https://dbschenker.sharepoint.com/sites/GlobalWorkplaceManagement/GlobalRollout/Shared%20Documents/PST%20Migrations/Country%20Rollout/APAC%20Regional%20Tracker.xlsx?web=1" } if ($comboboxinstance.Text -eq 'China') { $script:instance = 'cn' } $script:token = $textbox.text Write-Host "Connecting to RealMigrator Instance, please wait..." -ForegroundColor Cyan #Obtain the groups of the selected instance. $script:results = curl.exe -X GET --header "Accept: application/json" --header "Authorization: Bearer $token" "https://schenker$instance.realmigrator.com/api/admin/groups/*/members" -s | ConvertFrom-Json $listBoxClub.Items.Clear() #Obtain the groups of the selected instance. $script:groups = curl.exe -X GET --header "Accept: application/json" --header "Authorization: Bearer $token" "https://schenker$instance.realmigrator.com/api/admin/groups" -s | ConvertFrom-Json # Sort the objects for easy display $sorted = $groups | Sort-Object -Property DisplayName | select-Object DisplayName, ObjectID, Finalize #Add the displaynames to the List Box foreach ($grp in $sorted) { try { $listBoxClub.Items.Add($grp.displayname) } catch { Write-Host "The token you attempted to use is invalid or expired. Please request a new token from a Super Administrator for the requested instance." -ForegroundColor Red } } Get-InputLocationForm $button_refresh.Enabled = $True } $comboboxinstance.add_SelectedIndexChanged($comboboxinstance_SelectedIndexChanged) $listBoxClub.add_SelectedIndexChanged( { $group = $groups | Where-Object { $_.displayname -eq $listBoxClub.SelectedItem } if ($group) { $totalgroupusers = @() #Variable for group object ID $script:groupobjectid = $group.ObjectID # Variable for group displayname $script:groupdisplayname = $group.DisplayName #Get group members based on group object ID $groupmembers = curl.exe -X GET --header "Accept: application/json" --header "Authorization: Bearer $token" "https://schenker$instance.realmigrator.com/api/admin/groups/$groupobjectid/members" -s | ConvertFrom-Json #If there are no members say so if (!$groupmembers) { Write-Verbose "No users assigned to $groupdisplayname" $totalgroupusers = $unassignedusers.objectID if ($totalgroupusers.Count -eq 1) { $convertedusers = $totalgroupusers $convertedusers = $totalgroupusers | ConvertTo-Json -Compress $membersheader = "Members"":$convertedusers" } else { $convertedusers = $totalgroupusers $convertedusers = $totalgroupusers | ConvertTo-Json -Compress $membersheader = "Members"":$convertedusers" } } # Get user information else { $totalgroupusers = [array]$unassignedusers.objectID + [array]$groupmembers.objectid $convertedusers = $totalgroupusers $convertedusers = $totalgroupusers | ConvertTo-Json -Compress $membersheader = "Members"":$convertedusers" } $groupdisplayname = $groupdisplayname -replace '\s', '' #Convert the string to Json for use with curl $script:data = "{""ObjectID"":""$groupobjectid"",""DisplayName"":""$groupdisplayname"",""$membersheader}" | ConvertTo-Json Write-Host "JSON string created for API PUT command." $unassignedusers.Clear() } $button_execute.Enabled = $true }) $Form.Add_Shown( { $textBox.Select() }) [void]$Form.ShowDialog() } Function Get-InputLocationForm { Add-Type -AssemblyName System.Windows.Forms Add-Type -AssemblyName System.Drawing $Form = New-Object System.Windows.Forms.Form $Form.ClientSize = "290,120" $Form.TopMost = $false $Form.Text = "Location Code Search" $Form.StartPosition = "CenterScreen" $Font = New-Object System.Drawing.Font("Times New Roman", 12) $Form.Font = $Font $label_locationcode = New-Object System.Windows.Forms.Label $label_locationcode.Location = New-Object System.Drawing.Point(10, 10) $label_locationcode.Size = New-Object System.Drawing.Size(280, 20) $label_locationcode.Text = 'Enter location code to search for users:' $Form.Controls.Add($label_locationcode) $script:textBox_locationcode = New-Object System.Windows.Forms.TextBox $textBox_locationcode.Location = New-Object System.Drawing.Point(40, 40) $textBox_locationcode.Size = New-Object System.Drawing.Size(200, 20) $Form.Controls.Add($script:textBox_locationcode) #Search button start $button_searchreport = New-Object System.Windows.Forms.Button $button_searchreport.Location = New-Object System.Drawing.Size(50, 80) $button_searchreport.Size = New-Object System.Drawing.Size(180, 32) $button_searchreport.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right $button_searchreport.TextAlign = "MiddleCenter" $button_searchreport.Text = "Search" $button_searchreport.Enabled = $true $button_searchreport.Add_click( { Search-ByLocationCode; $Form.close() }) $Form.controls.add($button_searchreport) $Form.Add_Shown( { $textBox_locationcode.Select() }) [void]$Form.ShowDialog() } Function Search-ByLocationCode { Write-Host "Searching for users please wait..." #Start searching for users by the entered location code. $script:searchtext = $textBox_locationcode.Text #Open the Excel application. $Excel = New-Object -ComObject Excel.Application #Open the workbook located in SharePoint $workbook = $Excel.workbooks.open($script:source) #Array for users found in the master tracker. $usersinmastertracker = @() $script:unassignedusers = @() $i = 0 $worksheet = $workbook.sheets.item('RealMigrator Tracker') if ($worksheet.autofilter() -eq $null) { Write-Verbose "No filters to clear." } else { #Clear any filters from the worksheet $worksheet.autofilter.showalldata() } # Find Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel $Range = $Worksheet.Range("B1").EntireColumn $Found = $Range.find("$searchtext") #What If ($Found) { # Address Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel $BeginAddress = $Found.Address(0, 0, 1, 1) $colUPN = 1 $colLocationCode = 2 $username = $worksheet.Cells.Item($found.row, $colUPN) $location = $Worksheet.Cells.Item($found.row, $colLocationCode) #Initial Found Cell $properties = @{ UserPrincipalName = $username.text Location = $location.text Column = $Found.Column } $usersinmastertracker += New-Object psobject -Property $properties $i++ Do { $Found = $range.FindNext($Found) $Address = $Found.Address(0, 0, 1, 1) $username = $worksheet.Cells.Item($found.row, $colUPN) $location = $Worksheet.Cells.Item($found.row, $colLocationCode) If ($Address -eq $BeginAddress) { BREAK } $properties = @{ UserPrincipalName = $username.text Location = $location.text Column = $Found.Column } $usersinmastertracker += New-Object psobject -Property $properties Write-Progress -Activity 'Finding users in database - RealMigrator Global Master Tracker' -Status "Found $i users matching $searchtext" $i++ } Until ($False) } Else { Write-Warning "[$($WorkSheet.Name)] Nothing Found!" } $script:masterusers = $usersinmastertracker.userprincipalname Write-Host "Comparing known users in $searchtext against unassigned users in RealMigrator." $workbook.close($false) [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) [gc]::Collect() [gc]::WaitForPendingFinalizers() Remove-Variable excel -ErrorAction SilentlyContinue foreach ($user in $results) { $resultUPN = $user.upn if ($masterusers -contains ($resultUPN)) { $script:unassignedusers += $user | Select-Object UPN, ObjectID } else { continue } } Write-Host "Select a group to add the unassigned users to" -ForegroundColor Cyan if ($unassignedusers -ne $null) { Get-ListViewGroupSelection } else { [System.Windows.Forms.MessageBox]::Show("Cannot find any unassigned users by location code: $SearchText", 'No users found.') Get-InputLocationForm } } Function Get-ListViewGroupSelection { $listview.Items.Clear() $listview.Columns.Clear() $upnusers = $unassignedusers | Select-Object UPN #Populate information to the listview $properties = $upnusers[0].psObject.Properties $properties | ForEach-Object { $listview.Columns.Add("$($_.Name)") | Out-Null } foreach ($unassigneduser in $upnusers) { $userprincipalname = New-Object System.Windows.Forms.ListViewItem($unassigneduser.'UPN') $properties | Where-Object { $_.Name -ne 'UPN' } | ForEach-Object { $columnName = "$($_.Name)" # Add the custom item to the appropriate column. $userprincipalname.SubItems.add("$($unassigneduser.$columnName)") | out-null } # Finalize the listview item. $listview.Items.Add($userprincipalname) | out-null } # Resize the columns based on the size of the header. $listview.AutoResizeColumns("HeaderSize") } Function MessageBox { $script:messageresult = [System.Windows.Forms.MessageBox]::Show("Are you sure you want to move unassigned users to selected group?", 'Warning', 'YesNo', 'Warning') If ($messageresult -eq 'Yes') { Write-Host "Moving users..." -ForegroundColor Cyan curl.exe -X PUT "https://schenker$instance.realmigrator.com/api/admin/groups/$groupobjectID" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Bearer $token" -d $data -silence Write-Host "Unassigned users moved to $groupdisplayname" -ForegroundColor Green } else { Write-Warning 'Cancelling move' } } |