Creating a Microsoft 365 Automated Off-boarding Process with SharePoint, Graph API, and PowerShell

Creating a Microsoft 365 Automated Off-boarding Process with SharePoint, Graph API, and PowerShell

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:

  1. Licenses will be removed
  2. Mailbox will be auto forwarded to our forwarding user
  3. 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:

  1. Email: Single string
  2. Groups: Multiple lines
  3. Licenses: Multiple lines
  4. Mailbox Type: single string
  5. Forwarding Address: single string
  6. Status: Choice
  7. 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
1 forks.
5 stars.
0 open issues.

Recent commits:

 

Leave a Reply

Your email address will not be published. Required fields are marked *