Creating a Microsoft 365 Automated Off-boarding Process with SharePoint, Graph API, and PowerShell
Table of Contents
In this write-up I will be creating a basic off-boarding automation that uses SharePoint as the front end, and PowerShell, the Graph API, and Azure Runbooks as the back-end. HR will input the users UPN or Email, offboard date/time, and a forwarding address to forward email to. Once the off-boarding datetime is within 1hr the automation will check the user in Azure AD to ensure its valid, the forwarding user is valid in Azure AD, document in SharePoint the users e-mail address, any and all licenses, and all group memberships. After that, it will proceed with the off-boarding where it will remove all licenses from the user, remove all group memberships, and forward email to our forwarding user. It will log everything back to SharePoint where one can review it.
Off-Boarding Stages
Pending
In Pending we have just submitted our user and the automation has not seen it, or it has self-cleared from any and all errors it had in previous runs and is submitting the job back to automation. On next run, or first run, it will check to verify the user is found in Azure AD and the forwarding user is found in Azure AD.
Acknowledged
In this stage, automation has seen the user and confirmed that there are no issues with it. If the off-boarding datetime is to be done in 1 hours or less it will proceed with the off-boarding. The following will happen:
- Licenses will be removed
- Mailbox will be auto forwarded to our forwarding user
- Group Memberships will be removed
Complete
In this stage, the user has been off-boarded without issues.
Error
In this stage, there was an error encountered somewhere in the process. Lets say the user was inputted incorrectly and the automation could not find it in Azure AD. If we come back and see the error and change the UPN, the next time the automation runs it will re-check that user and attempt to self-clear itself. If that happens it will put itself back in Pending again so it can continue with the process as normal.
Logging
The automation will log every step of the process to a field in SharePoint. It will log information about the user, steps in the automation and any errors it comes across.
Submitting a User for Off-Boarding
In SharePoint, when submitting an new user to be off-boarded, HR is only asked a few details. The automation will do the rest.
Create SharePoint Front-End
First, we need to create a SharePoint site OR a new SharePoint list in an existing site. In my example, I am going to create a new Team Site
In my example, my site is for Human Resources as they will be in charge of the off-boarding process so I created a SharePoint site called “Human Resources”
Next, I am going to create a new List within the site.
My list will be called “User Offboarding”
Once I have my new SharePoint List, I want to add some columns that are a part of my off-boarding process. By default I have the “Title” column which will be the main column. Next, I will add the following columns:
- Email: Single string
- Groups: Multiple lines
- Licenses: Multiple lines
- Mailbox Type: single string
- Forwarding Address: single string
- Status: Choice
- Notes: multiple lines
I will also change “Title” to User for the users User Principal Name of UPN. Below is a description on the other fields
Email: Document the user’s e-mail address(s)
Groups: Document the user’s Groups before we clear them
Licenses: Document the user’s licenses before cleared
Mailbox Type: Write back the Mailbox type (shared or user mailbox) as we will be converting the mailbox to shared
Forwarding Address: Write back the user that email is forwarding to.
Status: The status the automation may be in.
Notes: Warning, errors, or success messages.
Once that is all done, my SharePoint list will look like the following screenshot:
Next, lets modify the Status to a ‘Choice’ selection. I made it Pending (default), Acknowledged, Error and Complete.
When a new user is entered it will get a default value of Pending, which means the automation has not seen this entry yet. Acknowledged means that automation has seen this user, Error means something was wrong and Complete means that the user was offboarded without issues.
Next, lets also add a date that the user should be offboarded. HR may pre-load users into the offboarding tool.
The OffboardDate value will look friendly in SharePoint but here is the value we will see in the back-end: 9/7/2022 7:00:00 AM
Get Column Information for Automation
Next, we need to get some back-end information from our SharePoint list using SharePoint PnP PowerShell Module. Install the PnP Module to proceed.
Install-Module -Name PnP.PowerShell
Using the PnP module connect to your SharePoint site that contains the list. In my example my site is: https://bwya77.sharepoint.com/sites/HumanResources
Next, use Get-PnPList cmdlet to get the ID for your newly created list. Take note of the ID value.
Get-PnPList -Identity "User Offboarding"
Next, we need to get the IDs of all the columns in our list using Get-PnPField. NOTE: The field “Title” will always be “Title” even though we re-named it.
Get-PnPField -List "User Offboarding"
Create Azure AD Application
The next item on the list is to create an Azure AD Application so we can interface with it and connect to the Microsoft Graph API. Naviagte to the Azure AD Portal and to to Azure Active Directory > App Registrations and select New Registration. Give your new Application a valid anme and a redirect URI. Take note of the Application (client) ID and tenantID value.
Next, go to Certificates and Secret and click “New Client Secret”
Note the secret value. You will not be able to view it again.
Using the following function, enter your clientID, tenantID and clientSecret and you should get back a bearer token.
Function Connect-GraphAPI {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$clientID,
[Parameter(Mandatory)]
[string]$tenantID,
[Parameter(Mandatory)]
[string]$clientSecret
)
begin {
$ReqTokenBody = @{
Grant_Type = "client_credentials"
Scope = "https://graph.microsoft.com/.default"
client_Id = $clientID
Client_Secret = $clientSecret
}
}
process {
$tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Method POST -Body $ReqTokenBody
}
end {
return $tokenResponse
}
}
Get Site ID
Next, we need to use the Graph API to get our siteID where our list is. In the App registration we need to grant the proper permissions.
In the Application in Azure Active Directory go to API Permissions and grant the Sites.ReadWrite.All (this may seem like overkill but we will need this permission later)
Next, connect to the Graph API and run the following code to display information on your sites. Take note of the id vaule
$token = Connect-GraphAPI -clientID '2a53-9b61-4b45-aa2-2453007ff7' -tenantID '643c9-54e9-4ce-981-f00c21f' -clientSecret 'rbx8Q~SaPZUKelYjWmmw4JfcWU'
$apiUrl = 'https://graph.microsoft.com/v1.0/sites/'
$Data = Invoke-RestMethod -Headers @{Authorization = "Bearer $($Token.access_token)"} -Uri $apiUrl -Method Get
$Sites = ($Data | select-object Value).Value
$Sites
View List Details and Data
Next, we must put some test data in our SharePoint List. If you do not do this, you will not see any fields in the next step.
With the data in place, we can now proceed to view our fields and items within the list using PowerShell. By running the code below, we can view our latest entry.
Function Get-ListItems {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$siteID,
[Parameter(Mandatory)]
[string]$listID,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/sites/$siteID/lists/$listID/items?expand=fields"
}
process {
$listItems = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $listItems.value.fields
}
}
Create our Automation Functions
Now we need to create the code base that will be doing the automation on our behalf. First lets create a function to easily search for our entered user.
Searchfor-User
The following function will take a UPN (which is my first field in my list, even though its named as User) and search Azure Active Directory for said user. Add the code to our script that contains the Get-ListItems function and the Connect-GraphAPI function.
You will also need to grant the following permission to your Application so you can run the API request: Directory.ReadWrite.All (again, this may seem like more permission than is needed but you will need this later on, so we don’t need to waste time granting just Directory.Read.All)
function Searchfor-User
{
param (
[system.string]$UPN,
[system.string]$AccessToken
)
Begin
{
$request = @{
Method = "Get"
Uri = "https://graph.microsoft.com/v1.0/users/?`$filter=(userPrincipalName eq '$UPN')"
ContentType = "application/json"
Headers = @{ Authorization = "Bearer $AccessToken" }
}
}
Process
{
$Data = Invoke-RestMethod @request
}
End
{
return $Data
}
}
Set-ListItemField
The next function we will create is the Set-ListItemField function which will write data back to items within the list. Pay close attention, the Field param is just taking in which field we want to modify but within the Body payload is where we set the cell. Each item within the body must match Exactly to the column names that we got earlier. Note that the User field is going to the Title, because we have to have that by default even though we renamed it. Also the Email column name is actually E_x002d_Mail because I had a ‘-‘.
function Set-ListItemField
{
Param (
[Parameter(Mandatory)]
[system.string]$AccessToken,
[Parameter(Mandatory)]
[System.String]$Field,
[Parameter(Mandatory)]
[System.Int32]$ItemNumber,
[Parameter(Mandatory)]
$Data,
[Parameter(Mandatory)]
[System.String]$SiteID,
[Parameter(Mandatory)]
[System.String]$ListID
)
Begin
{
If ($Field -eq "User") {
$Body = @"
{
"Title": "$Data"
}
"@
} ElseIf ($Field -eq "Email") {
$Body = @"
{
"E_x002d_Mail": "$Data"
}
"@
} ElseIf ($Field -eq "Notes") {
$Body = @"
{
"Notes": "$Data"
}
"@
} ElseIf ($Field -eq "Status") {
$Body = @"
{
"Status": "$Data"
}
"@
} ElseIf ($Field -eq "Licenses") {
$Body = @"
{
"Licenses": "$Data"
}
"@
} ElseIf ($Field -eq "MailboxType") {
$Body = @"
{
"MailboxType": "$Data"
}
"@
} ElseIf ($Field -eq "ForwardingAddress") {
$Body = @"
{
"ForwardingAddress": "$Data"
}
"@
} ElseIf ($Field -eq "MailboxFullAccess") {
$Body = @"
{
"MbxFullAccess": "$Data"
}
"@
} ElseIf ($Field -eq "Groups") {
$Body = @"
{
"Groups": "$Data"
}
"@
}
}
Process
{
$request = @{
Method = "Patch"
Uri = "https://graph.microsoft.com/v1.0/sites/$siteID/lists/$listID/items/$itemnumber/fields"
ContentType = "application/json"
Headers = @{ Authorization = "Bearer $($AccessToken)" }
Body = $Body
}
$Response = Invoke-RestMethod @request
}
End
{
return $Response
}
}
Get-MailboxSettings
Next, we need to create a function to get the mailbox type (regular or shared) so we can write it back to the SharePoint list. This is against the beta endpoint currently so things may change. Your application will need the following permission: MailboxSettings.ReadWrite
function Get-MailboxSettings {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/beta/users/$userPrincipalName/mailboxSettings"
}
process {
$mailboxSettings = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $mailboxSettings
}
}
Get-MailboxForwarding
Next, we need to check mail rules to check to see if we have enabled forwarding email for a user. Unfortunately the MSGraph API doesn’t allow us to modify the Exchange settings as well as we would like but we can still achieve mail forwarding through mail rules. I look to see if automation has set a forwarding rule by parsing the current rules and matching the display name.
function Get-MailboxForwarding {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter()]
[string]$RuleName = 'Automation - Offboarding Forwarding'
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/mailFolders/inbox/messageRules"
}
process {
$mailboxForwarding = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $mailboxForwarding.value | Where-Object {$_.DisplayName -eq $RuleName}
}
}
Set-MailboxForwarding
I must also set the mailbox forwarding as well. The function finds our user and then creates a top level mail flow rule within the mailbox. Note: if you have set external mail forwarding to be disabled within your tenant (which you should!) this will still allow you to put in an internal users email without issue. In my code, I am looking up the user internally to ensure the settings are correct when I create the rule.
function Set-MailboxForwarding {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$ForwardingAddress,
[Parameter()]
[string]$ForwardingName,
[Parameter()]
[string]$RuleName = 'Automation - Offboarding Forwarding'
)
begin {
$headers = @{
Authorization = "Bearer $($Token.access_token)"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/[email protected]/mailFolders/inbox/messageRules"
}
process {
#Search for our user in Azure AD. If you dont care to have your user be an internal user, you can skip this part and remove it
$FwdUser = Searchfor-User -UPN $ForwardingAddress -AccessToken $token.access_token
#if we found our fwding user
if ($FwdUser) {
$ForwardingName = $FwdUser.displayName
$ForwardingAddress = $FwdUser.mail
$params = @{
DisplayName = $RuleName
Sequence = 1
IsEnabled = $true
Actions = @{
ForwardTo = @(
@{
EmailAddress = @{
Name = $ForwardingName
Address = $ForwardingAddress
}
}
)
StopProcessingRules = $true
}
}
$body = $params | ConvertTo-Json -Depth 10
$mailboxForwarding = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method POST -Body $body -ContentType "application/json"
}
}
end {
return $mailboxForwarding
}
}
Remove-UserLicenses
The next item up, is to remove a users licenses. This will take a single LicenseSkuID so we can choose to remove all or just some of the licenses from a user.
function Remove-UserLicenses {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$LicenseSkuID
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/assignLicense"
}
process {
$body = @{
addLicenses = @()
removeLicenses= @($LicenseSkuID)
} | ConvertTo-Json -Depth 10
$removeLicense = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method POST -Body $body -ContentType "application/json"
}
end {
return $removeLicense
}
}
Remove-GroupMembership
Remove-GroupMembership will remove our user from any and all Azure AD Groups they are a member of.
function Remove-GroupMembership {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userID,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$GroupID
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/groups/$GroupID/members/$userID/`$ref"
}
process {
$removeGroupMember = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method DELETE
}
end {
return $removeGroupMember
}
}
Get-GroupMembership
Lastly, we want to get all the groups our user is a member of and write it back to the SharePoint list.
function Get-GroupMembership {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/memberOf"
}
process {
$groupMembers = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $groupMembers.value | where-object {$_.roleTemplateId -eq $null}
}
}
Automated Logic
Next, we need to create the automated logic behind the automation. Everything will be based on the status the user entry is in.
Pending: Automation has not seen the user (or has resolved errors it saw)
Acknowledged: Automation has seen the user and confirmed there are no issues with it (the user is in Azure AD, the forwarding user is in Azure AD)
Complete: The user has been off boarded without any issues.
Error: The user was not found in Azure AD or the forwarding user was not found in Azure AD.
Pending Status
In Pending status, the automation has either never seen this entry before or the entry has self-healed from errors. In this state we need to look up the user in Azure AD to verify that everything is correct and look up the forwarding user in Azure AD. If both of these pass, we can proceed.
Below is part of the script block for this logic. The entire automation runbook will be available later
if ($i.status -eq "Pending")
{
#Only search of the user if we have not done it prior
if ($i.notes -notlike "*User was found in Azure AD*")
{
if ($User) {
$Notes += "User was found in Azure AD`n"
#Set the email field in the SharePoint List
$Notes += "Email Address: $($User.mail)`n"
Set-ListItemField -AccessToken $token.access_token -Field "Email" -ItemNumber $i.id -Data $User.Mail -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Get all licenses for the user
$Licenses = Get-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
#Iterate through all licenses and create a clean array
$Licenses | foreach-object {
$licenseArray += "$($_.skupartnumber) `n"
}
#Get the mailbox type for the user (the property will be userPurpose)
$mailboxSettings = Get-MailboxSettings -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
#Write the mailbox type for the user
$Notes += "Mailbox Type: $($mailboxSettings.userPurpose)`n"
Set-ListItemField -AccessToken $token.access_token -Field "MailboxType" -ItemNumber $i.id -Data $mailboxSettings.userPurpose -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Write the licenses the user has back to the SharePoint List
$Notes += "Licenses: $($licenseArray)`n"
Set-ListItemField -AccessToken $token.access_token -Field "Licenses" -ItemNumber $i.id -Data $licenseArray -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Get the groups the user is a member of
$groupMembership = Get-GroupMembership -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
$grouparray = @()
$groupMembership | foreach-object {
$Notes += "Adding the Group: $($_.displayName)`n"
$grouparray += "$($_.displayName) `n"
}
Set-ListItemField -AccessToken $token.access_token -Field "Groups" -ItemNumber $i.id -Data $grouparray -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
Else {
$Notes += "User was not found in Azure AD`n"
#Set the status to Error
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Error" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
#Only search of the forwarding user if we have not done it prior
if ($i.notes -notlike "*Forwarding user was found in Azure AD*") {
#See if the forwarding user is in Azure Active Directory
$ForwardingUser = Searchfor-User -UPN $i.ForwardingAddress -AccessToken $token.access_token
if ($ForwardingUser) {
$Notes += "Forwarding user was found in Azure AD`n"
}
Else {
$Notes += "Forwarding user was not found in Azure AD`n"
#Set the status to Error
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Error" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
#If there were no errors, then change the status to Acknowledged
$Notes += "Setting status to Acknowledged`n"
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Acknowledged" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
Acknowledged
If the checks in the Pending stage completed, we can proceed to the next logic. In Acknowledged, we have confirmed the user information is correct and if the offboarding time is within 1hr we can proceed with the offboarding. In this stage we are going to remove all user licenses, set mailbox forwarding, and remove the user from all groups.
ElseIf ($i.status -eq "Acknowledged")
{
#Figure out how many days and hours until the user is to be off-boarded, if days left is less than or equal to 0 and hours is less than or equal to 0, then the user is to be off-boarded. NOTE: the default time in the timepicker is 7PM but can be changed in SharePoint
$Timespan = New-TimeSpan -Start (Get-Date) -End $i.OffboardDate
if (($Timespan.days -le 0) -and ($timespan.hours -le 0))
{
#Remove liceses from the user
#Get all licenses for the user
$Licenses = Get-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
foreach ($license in $Licenses) {
$Notes += "Removing $($license.skuPartNumber) license from $($i.Title)`n"
Remove-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token -licenseSkuID $license.skuId
}
#set the automatic mail forwarding rule
$Notes += "Setting automatic mail forwarding rule to forward email to $($i.ForwardingAddress)`n"
Set-MailboxForwarding -userPrincipalName $i.Title -accessToken $token.access_token -ForwardingAddress $i.ForwardingAddress
$MailRuleCheck = Get-MailboxForwarding -userPrincipalName $i.Title -accessToken $token.access_token
if ($MailRuleCheck) {
$Notes += "Mail forwarding rule was set`n"
}
else {
$Notes += "Mail forwarding rule was not set`n"
}
#Remove the user from the groups
$groups = Get-GroupMembership -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
foreach ($group in $groups) {
$Notes += "Removing $($user.DisplayName) from $($group.displayName)`n"
Remove-GroupMembership -userID $user.id -accessToken $token.access_token -groupID $group.id
}
#If there were no errors, then change the status to Complete
$Notes += "Setting status to Complete`n"
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Complete" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
Complete
If the status is moved into complete then the user was off boarded. (no code for this as we do not need to do anything).
Error
If the status is in an error state, that means that the user was not found in Azure AD (maybe we fat-fingered the UPN) OR the forwarding user was not found. When in an error state, the automation will attempt to self-clear these each run. If we saw it was in an error state and edited the entry to be correct, on the next run it will check, confirm the user information is now correct, delete the error message from the logs and then change the status to Pending.
NOTE: All errors must be cleared to be moved to Pending state. If only one of two errors is resolved it will still stay in an error state.
Elseif ($i.status -eq "Error")
{
#If we could not find the user in Azure AD, attempt to self clear
if ($i.notes -like "*User was not found*") {
$User = Searchfor-User -UPN $i.Title -AccessToken $token.access_token
if ($User) {
$Notes = $Notes.Replace("User was not found in Azure AD","")
}
}
#See if the error was because of the forwarding user not being in Azure AD
if ($i.notes -like "*Forwarding user was not found*") {
$ForwardingUser = Searchfor-User -UPN $i.ForwardingAddress -AccessToken $token.access_token
if ($ForwardingUser) {
$Notes = $Notes.Replace("Forwarding user was not found in Azure AD","")
}
}
If ($Notes -notlike "*not*")
{
#If our notes contain no errors, we know all have cleared and we can set the status to Pending again
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Pending" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
Set up the Azure Runbook
Create your Resource Group
First, I will create my resource group to house my runbook and automation account. The only important part here is to place everything in the same timezone as yourself, so when it does the datetime math we aren’t dealing with conversions.
Create Automation Account
Next, we need to create the automation account for the runbook.
Modify the Runbook
Next ,we need to store our secrets securely in the Automation Account. Go to Variables and add the clientID, clientSecret, and tenantID that we got earlier for our Azure AD application. Ensure that you select that they are encrypted. We will retrieve the values in the runbook by using the code below:
$clientId = Get-AutomationVariable -Name "clientID" $tenantID = Get-AutomationVariable -Name "tenantID" $clientSecret = Get-AutomationVariable -Name "clientSecret" #Connect to MSGraph API $token = Connect-GraphAPI -clientID $clientId -tenantID $tenantID -clientSecret $clientSecret
Create the Runbook
Next, we need to create the actual runbook. Here I selected 5.1 but PSCore will do as well.
Then populate the runbook with the code below. Please note, you will need to change the listID and siteID to match your own, as well as the field names if they do not match. Otherwise it is all re-usable.
Function Connect-GraphAPI {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$clientID,
[Parameter(Mandatory)]
[string]$tenantID,
[Parameter(Mandatory)]
[string]$clientSecret
)
begin {
$ReqTokenBody = @{
Grant_Type = "client_credentials"
Scope = "https://graph.microsoft.com/.default"
client_Id = $clientID
Client_Secret = $clientSecret
}
}
process {
$tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Method POST -Body $ReqTokenBody
}
end {
return $tokenResponse
}
}
Function Get-ListItems {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$siteID,
[Parameter(Mandatory)]
[string]$listID,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/sites/$siteID/lists/$listID/items?expand=fields"
}
process {
$listItems = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $listItems.value.fields
}
}
function Searchfor-User
{
param (
[system.string]$UPN,
[system.string]$AccessToken
)
Begin
{
$request = @{
Method = "Get"
Uri = "https://graph.microsoft.com/v1.0/users/?`$filter=(userPrincipalName eq '$UPN')"
ContentType = "application/json"
Headers = @{ Authorization = "Bearer $AccessToken" }
}
}
Process
{
$Data = Invoke-RestMethod @request
}
End
{
return $Data.value
}
}
function Set-ListItemField
{
Param (
[Parameter(Mandatory)]
[system.string]$AccessToken,
[Parameter(Mandatory)]
[System.String]$Field,
[Parameter(Mandatory)]
[System.Int32]$ItemNumber,
[Parameter(Mandatory)]
$Data,
[Parameter(Mandatory)]
[System.String]$SiteID,
[Parameter(Mandatory)]
[System.String]$ListID
)
Begin
{
If ($Field -eq "User") {
$Body = @"
{
"Title": "$Data"
}
"@
} ElseIf ($Field -eq "Email") {
$Body = @"
{
"E_x002d_Mail": "$Data"
}
"@
} ElseIf ($Field -eq "Notes") {
$Body = @"
{
"Notes": "$Data"
}
"@
} ElseIf ($Field -eq "Status") {
$Body = @"
{
"Status": "$Data"
}
"@
} ElseIf ($Field -eq "Licenses") {
$Body = @"
{
"Licenses": "$Data"
}
"@
} ElseIf ($Field -eq "MailboxType") {
$Body = @"
{
"MailboxType": "$Data"
}
"@
} ElseIf ($Field -eq "ForwardingAddress") {
$Body = @"
{
"ForwardingAddress": "$Data"
}
"@
} ElseIf ($Field -eq "MailboxFullAccess") {
$Body = @"
{
"MbxFullAccess": "$Data"
}
"@
} ElseIf ($Field -eq "Groups") {
$Body = @"
{
"Groups": "$Data"
}
"@
}
}
Process
{
$request = @{
Method = "Patch"
Uri = "https://graph.microsoft.com/v1.0/sites/$siteID/lists/$listID/items/$itemnumber/fields"
ContentType = "application/json"
Headers = @{ Authorization = "Bearer $($AccessToken)" }
Body = $Body
}
$Response = Invoke-RestMethod @request
}
End
{
return $Response
}
}
function Get-UserLicenses {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/licenseDetails"
}
process {
$userLicenses = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $userLicenses.value
}
}
function Get-MailboxSettings {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/beta/users/$userPrincipalName/mailboxSettings"
}
process {
$mailboxSettings = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $mailboxSettings
}
}
function Set-MailboxForwarding {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$ForwardingAddress,
[Parameter()]
[string]$ForwardingName,
[Parameter()]
[string]$RuleName = 'Automation - Offboarding Forwarding'
)
begin {
$headers = @{
Authorization = "Bearer $($Token.access_token)"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/[email protected]/mailFolders/inbox/messageRules"
}
process {
#Search for our user in Azure AD. If you dont care to have your user be an internal user, you can skip this part and remove it
$FwdUser = Searchfor-User -UPN $ForwardingAddress -AccessToken $token.access_token
#if we found our fwding user
if ($FwdUser) {
$ForwardingName = $FwdUser.displayName
$ForwardingAddress = $FwdUser.mail
$params = @{
DisplayName = $RuleName
Sequence = 1
IsEnabled = $true
Actions = @{
ForwardTo = @(
@{
EmailAddress = @{
Name = $ForwardingName
Address = $ForwardingAddress
}
}
)
StopProcessingRules = $true
}
}
$body = $params | ConvertTo-Json -Depth 10
$mailboxForwarding = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method POST -Body $body -ContentType "application/json"
}
}
end {
return $mailboxForwarding
}
}
function Get-MailboxForwarding {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter()]
[string]$RuleName = 'Automation - Offboarding Forwarding'
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/mailFolders/inbox/messageRules"
}
process {
$mailboxForwarding = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $mailboxForwarding.value | Where-Object {$_.DisplayName -eq $RuleName}
}
}
function Remove-UserLicenses {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$LicenseSkuID
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/assignLicense"
}
process {
$body = @{
addLicenses = @()
removeLicenses= @($LicenseSkuID)
} | ConvertTo-Json -Depth 10
$removeLicense = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method POST -Body $body -ContentType "application/json"
}
end {
return $removeLicense
}
}
function Get-GroupMembership {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userPrincipalName,
[Parameter(Mandatory)]
[string]$accessToken
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/users/$userPrincipalName/memberOf"
}
process {
$groupMembers = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method GET
}
end {
return $groupMembers.value | where-object {$_.roleTemplateId -eq $null}
}
}
function Remove-GroupMembership {
[CmdletBinding()]
Param (
[Parameter(Mandatory)]
[string]$userID,
[Parameter(Mandatory)]
[string]$accessToken,
[Parameter(Mandatory)]
[string]$GroupID
)
begin {
$headers = @{
Authorization = "Bearer $accessToken"
}
$apiUrl = "https://graph.microsoft.com/v1.0/groups/$GroupID/members/$userID/`$ref"
}
process {
$removeGroupMember = Invoke-RestMethod -Uri $apiURL -Headers $headers -Method DELETE
}
end {
return $removeGroupMember
}
}
$clientId = Get-AutomationVariable -Name "clientID"
$tenantID = Get-AutomationVariable -Name "tenantID"
$clientSecret = Get-AutomationVariable -Name "clientSecret"
#Connect to MSGraph API
$token = Connect-GraphAPI -clientID $clientId -tenantID $tenantID -clientSecret $clientSecret
#Get all items within the SharePoint List
$items = Get-ListItems -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -accessToken $token.access_token -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Iterate through all users
foreach ($i in $items)
{
#Get any and all notes that are already in the field so we do not overwrite anything
[array]$Notes = $i.Notes
$licenseArray = @()
#Search for our user
$User = Searchfor-User -UPN $i.Title -AccessToken $token.access_token
if ($i.status -eq "Pending")
{
#Only search of the user if we have not done it prior
if ($i.notes -notlike "*User was found in Azure AD*")
{
if ($User) {
$Notes += "User was found in Azure AD`n"
#Set the email field in the SharePoint List
$Notes += "Email Address: $($User.mail)`n"
Set-ListItemField -AccessToken $token.access_token -Field "Email" -ItemNumber $i.id -Data $User.Mail -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Get all licenses for the user
$Licenses = Get-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
#Iterate through all licenses and create a clean array
$Licenses | foreach-object {
$licenseArray += "$($_.skupartnumber) `n"
}
#Get the mailbox type for the user (the property will be userPurpose)
$mailboxSettings = Get-MailboxSettings -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
#Write the mailbox type for the user
$Notes += "Mailbox Type: $($mailboxSettings.userPurpose)`n"
Set-ListItemField -AccessToken $token.access_token -Field "MailboxType" -ItemNumber $i.id -Data $mailboxSettings.userPurpose -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Write the licenses the user has back to the SharePoint List
$Notes += "Licenses: $($licenseArray)`n"
Set-ListItemField -AccessToken $token.access_token -Field "Licenses" -ItemNumber $i.id -Data $licenseArray -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
#Get the groups the user is a member of
$groupMembership = Get-GroupMembership -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
$grouparray = @()
$groupMembership | foreach-object {
$Notes += "Adding the Group: $($_.displayName)`n"
$grouparray += "$($_.displayName) `n"
}
Set-ListItemField -AccessToken $token.access_token -Field "Groups" -ItemNumber $i.id -Data $grouparray -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
Else {
$Notes += "User was not found in Azure AD`n"
#Set the status to Error
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Error" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
#Only search of the forwarding user if we have not done it prior
if ($i.notes -notlike "*Forwarding user was found in Azure AD*") {
#See if the forwarding user is in Azure Active Directory
$ForwardingUser = Searchfor-User -UPN $i.ForwardingAddress -AccessToken $token.access_token
if ($ForwardingUser) {
$Notes += "Forwarding user was found in Azure AD`n"
}
Else {
$Notes += "Forwarding user was not found in Azure AD`n"
#Set the status to Error
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Error" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
#If there were no errors, then change the status to Acknowledged
$Notes += "Setting status to Acknowledged`n"
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Acknowledged" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
ElseIf ($i.status -eq "Acknowledged")
{
#Figure out how many days and hours until the user is to be off-boarded, if days left is less than or equal to 0 and hours is less than or equal to 0, then the user is to be off-boarded. NOTE: the default time in the timepicker is 7PM but can be changed in SharePoint
$Timespan = New-TimeSpan -Start (Get-Date) -End $i.OffboardDate
if (($Timespan.days -le 0) -and ($timespan.hours -le 0))
{
#Remove liceses from the user
#Get all licenses for the user
$Licenses = Get-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
foreach ($license in $Licenses) {
$Notes += "Removing $($license.skuPartNumber) license from $($i.Title)`n"
Remove-UserLicenses -userPrincipalName $user.userPrincipalName -accessToken $token.access_token -licenseSkuID $license.skuId
}
#set the automatic mail forwarding rule
$Notes += "Setting automatic mail forwarding rule to forward email to $($i.ForwardingAddress)`n"
Set-MailboxForwarding -userPrincipalName $i.Title -accessToken $token.access_token -ForwardingAddress $i.ForwardingAddress
$MailRuleCheck = Get-MailboxForwarding -userPrincipalName $i.Title -accessToken $token.access_token
if ($MailRuleCheck) {
$Notes += "Mail forwarding rule was set`n"
}
else {
$Notes += "Mail forwarding rule was not set`n"
}
#Remove the user from the groups
$groups = Get-GroupMembership -userPrincipalName $user.userPrincipalName -accessToken $token.access_token
foreach ($group in $groups) {
$Notes += "Removing $($user.DisplayName) from $($group.displayName)`n"
Remove-GroupMembership -userID $user.id -accessToken $token.access_token -groupID $group.id
}
#If there were no errors, then change the status to Complete
$Notes += "Setting status to Complete`n"
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Complete" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
Elseif ($i.status -eq "Error")
{
#If we could not find the user in Azure AD, attempt to self clear
if ($i.notes -like "*User was not found*") {
$User = Searchfor-User -UPN $i.Title -AccessToken $token.access_token
if ($User) {
$Notes = $Notes.Replace("User was not found in Azure AD","")
}
}
#See if the error was because of the forwarding user not being in Azure AD
if ($i.notes -like "*Forwarding user was not found*") {
$ForwardingUser = Searchfor-User -UPN $i.ForwardingAddress -AccessToken $token.access_token
if ($ForwardingUser) {
$Notes = $Notes.Replace("Forwarding user was not found in Azure AD","")
}
}
If ($Notes -notlike "*not*")
{
#If our notes contain no errors, we know all have cleared and we can set the status to Pending again
Set-ListItemField -AccessToken $token.access_token -Field "Status" -ItemNumber $i.id -Data "Pending" -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
}
#At the end: write all notes to the list
Set-ListItemField -AccessToken $token.access_token -Field "Notes" -ItemNumber $i.id -Data $Notes -listID '1baffb5c-d51a-4803-b534-2a83c3c867fd' -siteID 'bwya77.sharepoint.com,218d5607-899f-4ec4-888a-0657c4fa2b11,af51a2a9-880d-4109-a7b1-84962fafb8a2'
}
Set a Recurrence
Lastly, I want it to run automatically every 1 hour. So I will create a schedule for the runbook to run every 1 hours.
Putting it all together
After 1 hour has passed. I can see in Azure that it ran without issue.
And then in the Front-End I can see that it ran and there were no issues with my user. Next time it runs by user will be off-boarded (unless of course I change the offbaording date!)
You can build upon this and even do things such as email IT when a new user was submitted, email the users manager when they are offboarded, send a Teams chat, etc! I firstly wanted to show how to set it up in a basic configuration first. Now I can tell my HR to use the forum to proceed with any off-boardings. I locked the SharePoint site to just them so nobody else can access it.
Source Code
I will attempt to change the code here but up to date code can be found on GitHub
https://github.com/bwya77/SharePointOffBoarding/
My name is Bradley Wyatt; I am a 5x Microsoft Most Valuable Professional (MVP) in Microsoft Azure and Microsoft 365. I have given talks at many different conferences, user groups, and companies throughout the United States, ranging from PowerShell to DevOps Security best practices, and I am the 2022 North American Outstanding Contribution to the Microsoft Community winner.