Powershell

1. Introduction #

Powershell is a scripting language with a wealth of functionality that can be used for all sorts of purposes. Typically in RPA contexts, it will be used in connection with Active Directory. It can be used for extraction or general user administration as examples. The section below requires that you are initially somewhat familiar with Powershell or programming in order to be able to use it. Unlike other sections, it is such a broad topic that it is difficult to find sandboxes or tutorials on how to learn how to use it. The latter typically depends on what you are going to use it for.

Name	Note	Link
Generally	Microsoft’s main page for Powershell	https://learn.microsoft.com/en-us/powershell/
Course	A free course with an overview of Powershell	https://www.codecademy.com/learn/learn-powershell
AD Commands	Large list of commands you can use for AD	https://activedirectorypro.com/powershell-commands/#ad
AD User Mapping	What is located where on a User Object in AD is also represented visually in section 3.2.	https://learn.microsoft.com/da-dk/windows/win32/ad/user-object-user-interface-mapping?redirectedfrom=MSDN
Exchange/Online	I haven’t been able to find any test cases in my work yet, so only an example has been made below. 1.1.. Genbrugelige scripts . Se eventuelt reference for at lære mere.	https://learn.microsoft.com/en-us/training/modules/manage-exchange-online-use-windows-powershell/?source=recommendations

1.1 Reusable scripts #

Below is a list of Scripts that may be useful for RPA purposes.

Description	Script
Reset Powershell ISE session, removes saved variables and modules from the session ( used instead of closing and opening the application )	Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear();
Export output to CSV as a table with ÆØÅ Alternatively, if you need expanded properties (these will be some strange data when it comes out as a file otherwise)	\| export-csv -Path “C:\Users\< username> \Documents\Test.csv” -NoTypeInfo -Delim “;” -encoding utf8 \| out-file “C:\Users\< username> \Documents\Test.csv” -encoding utf8 -append
Change column names for output	get-aduser \| select @{Name=”Name”;Expression={$_.Name}}, @{Name=”Username”;Expression={$_.SamAccountName}}*
Search by name and extract username	get-aduser -Filter {Name -like “Jens J”} \| Select-Object SamAccountName*
Get all members out of AD group, username and full name	Get-ADGroupMember -Identity app-kmd \| select samaccountname, name
To check which phone numbers can be reused, for example if the user is about to be deleted, or to extract a list of who has which number	Get-ADUser -Filter -SearchBase ‘OU=Delete_Ready,OU=Users,DC=TEST’ -properties telephoneNumber \| where {$_.telephoneNumber -ne $null} \| Select SamAccountName, telephoneNumber*
List of who is affiliated with this office	Get-ADUser -LDAPFilter “(PhysicalDeliveryOfficeName=Economy Department)” \| select Name, SamAccountName \| Format-list Name, SamAccountName
Retrieve members from shared mailboxes in Exchange and save the extract as a .txt file	#Connect to Exchange Connect-ExchangeOnline #Get mailboxes from txt file as list/array $mailboxArray = Get-Content -Path “C:\temp\Mailboxes.txt” foreach ($mailbox in $mailboxArray) { #Append in the final file, so that the mailbox name is at the top, and then the extract Add-Content C:\temp\test.txt “`n$mailbox” # Get members of the mailbox Get-Mailbox -Identity “$mailbox” -ResultSize:Unlimited \| Get-MailboxPermission \| Select-Object User \| out-file “C:\temp\test.txt” -encoding utf8 -append } #Close connection to Exchange Disconnect-ExchangeOnline –Confirm:$false
To search for text in Word and replace with an image	# Add assembly Add-Type -AssemblyName Microsoft.Office.Interop.Word $imageFilePath = “” # Find the existing Word process $word = [Runtime .Interopservices.Marshal]::GetActiveObject(‘Word.Application’) $word.Visible = $true # Attach to the open document $document = $word.ActiveDocument # Find the text “[Indsæt billede]” and is replaced with the image $findText = “[Indsæt billede]” $range = $document.Content $range.Find.Execute($findText) if ($range.Find.Found) { $range.Select() $selection = $word.Selection $selection.InlineShapes.AddPicture($imageFilePath ) } # Release COM objects [System .Runtime.Interopservices.Marshal]::ReleaseComObject($selection) \| Out-Null [System .Runtime.Interopservices.Marshal]::ReleaseComObject($document) \| Out-Null [System .Runtime.Interopservices.Marshal]::ReleaseComObject($word) \| Out-Null
To search for text in Word behind a link, replace with text, and then convert to a hyperlink	# Add assemblies Add-Type -AssemblyName Microsoft.Office.Interop.Word Add-Type -AssemblyName System.Windows.Forms # Find the existing Word process $word = [Runtime .Interopservices.Marshal]::GetActiveObject(‘Word.Application’) $word.Visible = $true # Attach to the open document $document = $word.ActiveDocument # Search and replace $findText = “=eDocsagsnummer” $replaceText = “=” $selection = $word.Selection $selection.Find.Text = $findText $selection.Find.Replacement.Text = $replaceText $selection.Find.Execute([ref]$findText, [ref]$false, [ref]$true, [ref]$false, [ref]$false, [ref]$false, [ref]$true, [ref]$false, [ref]$false, [ref]$replaceText, [ref]2) # Search for the text in $searchText = “=” $selection.Find.Text = $searchText $selection.Find.Execute([ref]$searchText) # Send an “End” to go to the end of the link, and a “Space” to force Word to turn the text into a hyperlink [System .Windows.Forms.SendKeys]::SendWait(“{END}”) Start-Sleep -Seconds 1 [System .Windows.Forms.SendKeys]::SendWait(” “) # Cleanup [System .Runtime.Interopservices.Marshal]::ReleaseComObject($selection) \| Out-Null [System .Runtime.Interopservices.Marshal]::ReleaseComObject($document) \| Out-Null [System .Runtime.Interopservices.Marshal]::ReleaseComObject($word) \| Out-Null
Some RPA applications, such as Power Automate Desktop , have their own way of escaping special characters. This can cause problems when using the variables in PowerShell. The easiest solution is to use here-strings ( @’…’@ ) in PowerShell instead of trying to escape special characters before using them in PowerShell. This will automatically cause PowerShell to keep the characters exactly as they are. Additionally, it may be easier to create a PSCustomObject and convert to JSON afterwards, as there are syntax issues with using here-strings in JSON strings.	$body =[PSCustomObject] @{ startdate = “2024-12-24” enddate = “2025-01-01” title = @’ %JSONTitle% ‘@ content = @’ %JSONContent% ‘@ } $body = ConvertTo-Json $body -Depth 100

2. Installation of miscellaneous #

2.1 AD Integration – Windows RSAT #

RPA software typically cannot work directly in the AD UI, as they can only see the window itself, not the contents of it. Instead, you can use PowerShell Script for this, which requires installing Windows RSAT (Remote Server Administration Tools) on the PC in question. See installation guide here – https://activedirectorypro.com/install-rsat-remote-server-administration-tools-windows-10/#rsat-powershell

Output from a script is usually in a long text, however, you can remove the white-space , and convert it to a list with newline delimiters, and then delete the top two items in the list, as these are often a heading before the output itself.

2.2 Exchange Online Powershell Module #

If you use Office365 and have Exchange Online, you can use the steps below to access via Powershell. If you use multi-factor, there may be more things needed to get it working, see the installation guide from Microsoft below.

Installation guide here – https://learn.microsoft.com/en-us/powershell/exchange/exchange-online-powershell-v2?view=exchange-ps#install-and-maintain-the-exchange-online-powershell-module

Start Powershell as administrator and run the following.

Install-Module –Name ExchangeOnlineManagement

Import-Module ExchangeOnlineManagement

Command to check the version of downloaded module – Get-Module -Name ExchangeOnlineManagement -ListAvailable

3. Active Directory #

It is used to test scripts through Windows PowerShell ISE . Here you can also enable a panel so that you can write the code and edit it, easier than if you had to write directly in the console. This can be done as shown below.

3.1 Anatomy of an AD Extract in PowerShell #

In general, you can divide a PowerShell AD extract into six potential parts, where the order is always fixed, part 1 and part 3 will always be present at minimum. After each number part, it ends with a | which acts as a kind of stop sign for the next part

get-aduser or get-adgroupmember are the most commonly used extracts you will encounter
- -filter / -ldapfilter / -identity is almost always required, below you can see how this can be set
  - -filter * – Extracts all data about searched user( s )
  - -filter {surname -like ‘Nielsen’} – Extracts all data about users with the last name Nielsen
  - -filter {telephonenumber -like ‘*’} – Extracts all data about users with a telephone number ( that is, as long as this field is not blank / null in AD )
  - -LDAPFilter “(Department=IT)” – Extracts all data with users working in the IT department
  - -identity username – used to look up a user on SamAccountName
- Optional parameters
  - -searchbase ‘OU=Users,DC=Main’ – if you want to search a specific OU in AD, rather than the entire AD
    - Note: You must include the entire path to the OU.
  - -properties – used if you want to get more data than the standard name, username, and miscellaneous.
    - See visual guide further down in this guide, separated by , or * for all data
where or where-object for further filtering of your extract, if you need to specify additional, optional parameters
select here we specify exactly the data we want to extract
- Comma separated for multiple extractions
- -unique can be set if you only want to see unique hits
- @{Name=”ColumnName”;Expression{$_ParameterName}} can be used if you want the extract to have different column names, note that each parameter in the extract must be specified.
sort or sort-object if the extract should be sorted AZ according to one of the parameters
- Comma separated, first parameter has priority if you want to sort on more than one column
format-table or format-list if you want to change the display / order of your extract
- Both use -property to sort
export-csv and out-file can be used to save the extract as a file, csv or text document
- export-csv -Path “C:\Users\dwithnn\Documents\test.csv” -NoTypeInfo -Delim “;” – encoding utf8
  - -encoding utf8 is used to include ÆØÅ in the extract
- out-file “C:\Users\dwithnn\Documents\test.txt” -encoding utf8
  - Can also be created as .csv, however it will not be created as a semicolon separated csv file, which can be a bit of a hassle.