Retrieve Office 365 user details with Azure Functions, Microsoft Flow and SharePoint Online

Microsoft Flow fever has taken hold in our office this week, and we’re quickly finding new ways to automate our administration and user management tasks.

What is Microsoft Flow?

Microsoft Flow is an even more user friendly version of Azure Logic Apps, and it has been billed as a replacement for SharePoint workflow creation following the discontinuation of SharePoint Designer. Microsoft Flow is similar to IFTTT, Zapier, and it’s identical in many ways to Azure Logic Apps. In my opinion, the best thing about it is it’s deep integrations with Microsoft services, especially those within Office 365.

Using Microsoft Flow you can easily automate processes and data flow between services. Including SharePoint/OneDrive, Exchange/Outlook, Power BI, and Azure Storage – as well as a host of third party services like Twitter, Dropbox and MailChimp. It’s also simple to extend and integrate with other services via its ability to trigger from, or initiate, HTTP calls.

How can you use it to manage Office 365 Users?

Part 1 of this solution demonstrates how to build an automatically populated and updated SharePoint Online list containing the details of users in delegated Office 365 tenants.

In part 2, we’ll use Azure Functions to create Exchange Administrators in all of our delegated tenants that we can use to perform tasks not possible via delegated administration

In part 3, we’ll look at adding a Microsoft Flow and Azure Function to easily off-board users who have left their respective companies.

The entire solution looks like this.

• An Azure function, running daily, collects all user details sends them to a Microsoft Flow
• A Microsoft Flow receives the users, checks whether the user has been added to SharePoint, then creates or updates an item in a SharePoint list.
• Another Azure Function creates Exchange Administrator users in all delegated tenants, and blocks their credentials until they’re required.
• A second Microsoft Flow can be manually triggered on these SharePoint items to automatically offboard selected users. We’ve added an approval step to the flow to prevent accidental offboarding of users. Once the offboarding has been approved within the Microsoft Flow, a second Azure Function is triggered which offboards the selected user.

Get Office 365 user details from delegated tenants and send them to a Microsoft Flow

The first Azure function

To set up the first Azure Function, you’ll need to create a Timer Triggered PowerShell function with function level authentication.

For instructions on how to set this up, see our knowledge base article here. Remember to upload the MSOnline PowerShell module and a PassEncryptKey file via FTP into the function. We’ve set ours to run 5 times a week using the following CRON schedule:

0 30 9 * * 1-5

This function is called MsolUserReporting and looks like this:

Write-Output "PowerShell Timer trigger function executed at:$(get-date)";

$FunctionName = 'MsolUserReporting'
$ModuleName = 'MSOnline'
$ModuleVersion = '1.1.166.0'
$username = $Env:user
$pw = $Env:password
#import PS module
$PSModulePath = "D:\home\site\wwwroot\$FunctionName\bin\$ModuleName\$ModuleVersion\$ModuleName.psd1"
$res = "D:\home\site\wwwroot\$FunctionName\bin"
 
Import-module $PSModulePath
 
# Build Credentials
$keypath = "D:\home\site\wwwroot\$FunctionName\bin\keys\PassEncryptKey.key"
$secpassword = $pw | ConvertTo-SecureString -Key (Get-Content $keypath)
$credential = New-Object System.Management.Automation.PSCredential ($username, $secpassword)
 
# Connect to MSOnline
 
Connect-MsolService -Credential $credential

function ConvertDateToInvarient($standardDate) {

    if ($standardDate.Month -lt 10) {
        $InvarientMonth = "0$($standardDate.Month)"
    }
    else {
        $InvarientMonth = $standardDate.Month
    }

    if ($standardDate.Day -lt 10) {
        $InvarientDay = "0$($standardDate.Day)"
    }
    else {
        $InvarientDay = $standardDate.Day
    }

    $InvarientDate = "$InvarientMonth/$InvarientDay/$($StandardDate.Year) $($StandardDate.TimeOfDay)"

    return $InvarientDate

}

# Create a collection of all companies, including your own, to iterate through and collect user details.

$Companies = @()
$Companies = Get-MsolPartnerContract | Select-Object Tenantid
$OwnCompanyInfo = Get-MsolCompanyInformation | Select-Object ObjectId
$OwnCompanyTenantId = New-Object PsObject
Add-Member -InputObject $OwnCompanyTenantId -MemberType NoteProperty -Name TenantId -Value $OwnCompanyInfo.ObjectId
$Companies += $OwnCompanyTenantId

# Define an empty collection for all users.

$allusers = @()

# Define the script block to be run on Exchange using delegated administration

$GetMailboxScriptBlock = {Get-Mailbox -ResultSize Unlimited}

foreach ($Company in $Companies) {

    # Retrieve the information from each tenant to set up a delegated connection to Exchange Online
	
    $InitialDomain = Get-MsolDomain -TenantId $Company.TenantId | Where {$_.IsInitial -eq $true}

    $DelegatedOrgURL = "https://ps.outlook.com/powershell-liveid?DelegatedOrg=" + $InitialDomain.Name
	
    # Retrieve all user mailboxes from the customer tenant

    $Mailboxes = Invoke-Command -ConnectionUri $DelegatedOrgURL -Credential $Credential -Authentication Basic -ConfigurationName Microsoft.Exchange -AllowRedirection -ScriptBlock $GetMailboxScriptBlock -HideComputerName

    $CompanyName = Get-MsolCompanyInformation -TenantId $Company.TenantId

    Write-Output "Retrieving data for $($CompanyName.DisplayName)"

    $CompanyUsers = Get-MsolUser -TenantId $Company.TenantId -all

    foreach ($CompanyUser in $CompanyUsers) {

        $UserMailbox = $Mailboxes | Where-Object {$_.ExternalDirectoryObjectId -contains $CompanyUser.objectid}

        $LicenseString = " "

        foreach ($license in $CompanyUser.Licenses) {
            $LicenseString = "$licenseString $($license.AccountSkuId),"
        }

        if ($LicenseString.length -gt 0) {
            $LicenseString = $LicenseString.Substring(0, $LicenseString.Length - 1)
        }

        # Convert the date objects to Invarient Date formatted strings for SharePoint

        $WhenCreatedInvarient = ConvertDateToInvarient($CompanyUser.WhenCreated)
        $LastPasswordChangeTimestampInvarient = ConvertDateToInvarient($CompanyUser.LastPasswordChangeTimestamp)
		
        # Add the custom values to the CompanyUser object

        $CompanyUser = $CompanyUser | Add-Member @{TenantId = $Company.tenantId; CompanyName = $CompanyName.DisplayName; LicenseString = $LicenseString; `
                WhenCreatedInvarient = $WhenCreatedInvarient; LastPasswordChangeTimestampInvarient = $LastPasswordChangeTimestampInvarient; RecipientTypeDetails = $UserMailbox.RecipientTypeDetails
        } -PassThru
		
        # Add the CompanyUser object to the allusers collection

        $allusers += $CompanyUser
    }
}

# Once complete, send all users to Microsoft Flow in a single HTTP call

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("content-type", 'application/json')

Invoke-RestMethod -Uri "EnterURLFromMicrosoftFlowHere" -Method Post -Body (ConvertTo-Json $allUsers) -Headers $headers -Verbose

The function pulls the user data for your own tenant and all of your customers delegated tenants. It adds a few extra values into each $CompanyUser object. This extra data does the following:

• Associates the users with their company in SharePoint
• Retrieved the mailbox type – eg. UserMailbox/SharedMailbox
• Formats the date values into the invarient date format required by SharePoint
• Creates a string for the licenses array which is easier to read

In this example, we are pulling the following user values, though you can take more or less if you like:

• UserPrincipalName
• ProxyAddresses
• DisplayName
• CompanyName
• ObjectId
• TenantId
• Licenses
• IsLicensed
• BlockCredential
• LastPasswordChangeTimestamp
• DateCreated
• RecipientTypeDetails

Once all values are collected, it will use the Invoke-RestMethod cmdlet to send them to Microsoft Flow.

Create a SharePoint List to hold the user data

1. Go to a SharePoint site and build a new list by clicking Site Contents on the left menu, then + New > List
2. Create a list called Office 365 Users (or whatever you like)
3. Then open the list up and rename the Title column to Display Name.
4. Then add more columns by clicking the + icon at the top of the list and choosing the Type and Name for each one.
5. Our SharePoint List has the following configuration.
6. I also recommend that you change SharePoint List permissions. Since this list will contain a lot of personal user info, and the potential ability to offboard any of the users included in it, it’s a good idea to adjust the permissions on the list so that only the appropriate people can see it.

Add or Update Office 365 User details in a SharePoint List with Microsoft Flow

The first version of this flow received a single users details in each call and executed over 3400 times whenever we ran the Azure Function. It took about an hour to complete.

Since Microsoft Flows are priced based on the number of executions, I decided to run all users in a single flow. This single flow now runs overnight, takes about 7 hours to complete and processes all 3400 users one by one. The long running flow isn’t an issue for our use case.

This flow runs in the following order:

• Receive the JSON Payload containing all user data
• Iterate through each user
• Check whether the user exists in SharePoint using the SharePoint – Get Items action, filtering by the user’s unique Object ID
• Do some more string work to get around the nested ‘for each’ limitations in Microsoft Flow
• If the user’s details have already been added to SharePoint, do some extra string work to extract the SharePoint item ID from the earlier SharePoint – Get Items output, then update the item in SharePoint.
• If the user’s details aren’t in SharePoint, create a new item in the list with the details.

Create a new Microsoft Flow

1. Visit flow.microsoft.com and sign in with your Office 365 username and password.
2. Click + Create from blank under My Flows.
3. We’re going to trigger this flow with the final HTTP call from our Azure Function. Under Search all services and triggers, search for Request and choose Request/Response.
4. This Microsoft Flow will now start whenever it receives a HTTP Request from our Azure Function.

Retrieving the JSON Payload to build the JSON schema

I built and tested our Azure function in PowerShell ISE. Building it locally allows you to monitor it’s HTTP calls using Fiddler.

You can use Fiddler to open the HTTP call sent by PowerShell ISE, then copy the JSON Payload directly into the Azure Function to generate the JSON body schema. The Microsoft Flow that receives the request can use an example JSON Payload to build a schema model of the JSON data.

So if you’re deviating from our JSON payload, then you can use Powershell ISE and Fiddler or similar to retrieve the payload and generate the schema in Microsoft Flow. Otherwise if you’re copying our Azure Function code exactly as above, the JSON Schema is as follows. Double click to select it, then copy and paste this into the Request Body JSON Schema field:

{"type":"array","items":{"type":"object","properties":{"ExtensionData":{"type":"object","properties":{}},"AlternateEmailAddresses":{"type":"array","items":{"type":"string"}},"AlternateMobilePhones":{"type":"array"},"AlternativeSecurityIds":{"type":"array"},"BlockCredential":{"type":"boolean"},"City":{"type":"string"},"CloudExchangeRecipientDisplayType":{"type":"number"},"Country":{"type":"string"},"Department":{"type":"any"},"DirSyncProvisioningErrors":{"type":"array"},"DisplayName":{"type":"string"},"Errors":{"type":"any"},"Fax":{"type":"string"},"FirstName":{"type":"string"},"ImmutableId":{"type":"any"},"IndirectLicenseErrors":{"type":"array"},"IsBlackberryUser":{"type":"boolean"},"IsLicensed":{"type":"boolean"},"LastDirSyncTime":{"type":"any"},"LastName":{"type":"string"},"LastPasswordChangeTimestamp":{"type":"string"},"LicenseReconciliationNeeded":{"type":"boolean"},"Licenses":{"type":"array","items":{"type":"string"}},"LiveId":{"type":"string"},"MSExchRecipientTypeDetails":{"type":"any"},"MobilePhone":{"type":"string"},"ObjectId":{"type":"string"},"Office":{"type":"any"},"OverallProvisioningStatus":{"type":"number"},"PasswordNeverExpires":{"type":"boolean"},"PasswordResetNotRequiredDuringActivate":{"type":"any"},"PhoneNumber":{"type":"string"},"PortalSettings":{"type":"array"},"PostalCode":{"type":"string"},"PreferredDataLocation":{"type":"any"},"PreferredLanguage":{"type":"string"},"ProxyAddresses":{"type":"array","items":{"type":"string"}},"ReleaseTrack":{"type":"any"},"ServiceInformation":{"type":"array","items":{"type":"string"}},"SignInName":{"type":"string"},"SoftDeletionTimestamp":{"type":"any"},"State":{"type":"string"},"StreetAddress":{"type":"string"},"StrongAuthenticationMethods":{"type":"array","items":{"type":"string"}},"StrongAuthenticationPhoneAppDetails":{"type":"array","items":{"type":"string"}},"StrongAuthenticationProofupTime":{"type":"any"},"StrongAuthenticationRequirements":{"type":"array","items":{"type":"string"}},"StrongAuthenticationUserDetails":{"type":"object","properties":{"ExtensionData":{"type":"string"},"AlternativePhoneNumber":{"type":"any"},"Email":{"type":"string"},"OldPin":{"type":"any"},"PhoneNumber":{"type":"string"},"Pin":{"type":"any"}}},"StrongPasswordRequired":{"type":"boolean"},"StsRefreshTokensValidFrom":{"type":"string"},"Title":{"type":"any"},"UsageLocation":{"type":"string"},"UserLandingPageIdentifierForO365Shell":{"type":"any"},"UserPrincipalName":{"type":"string"},"UserThemeIdentifierForO365Shell":{"type":"string"},"UserType":{"type":"any"},"ValidationStatus":{"type":"number"},"WhenCreated":{"type":"string"},"WhenCreatedInvarient":{"type":"string"},"CompanyName":{"type":"string"},"RecipientTypeDetails":{"type":"string"},"LicenseString":{"type":"string"},"LastPasswordChangeTimestampInvarient":{"type":"string"},"TenantId":{"type":"string"}},"required":["ExtensionData","AlternateEmailAddresses","AlternateMobilePhones","AlternativeSecurityIds","BlockCredential","City","CloudExchangeRecipientDisplayType","Country","Department","DirSyncProvisioningErrors","DisplayName","Errors","Fax","FirstName","ImmutableId","IndirectLicenseErrors","IsBlackberryUser","IsLicensed","LastDirSyncTime","LastName","LastPasswordChangeTimestamp","LicenseReconciliationNeeded","Licenses","LiveId","MSExchRecipientTypeDetails","MobilePhone","ObjectId","Office","OverallProvisioningStatus","PasswordNeverExpires","PasswordResetNotRequiredDuringActivate","PhoneNumber","PortalSettings","PostalCode","PreferredDataLocation","PreferredLanguage","ProxyAddresses","ReleaseTrack","ServiceInformation","SignInName","SoftDeletionTimestamp","State","StreetAddress","StrongAuthenticationMethods","StrongAuthenticationPhoneAppDetails","StrongAuthenticationProofupTime","StrongAuthenticationRequirements","StrongAuthenticationUserDetails","StrongPasswordRequired","StsRefreshTokensValidFrom","Title","UsageLocation","UserLandingPageIdentifierForO365Shell","UserPrincipalName","UserThemeIdentifierForO365Shell","UserType","ValidationStatus","WhenCreated","WhenCreatedInvarient","CompanyName","RecipientTypeDetails","LicenseString","LastPasswordChangeTimestampInvarient","TenantId"]}}

In order to start this flow, we’ll need the HTTP POST URL from this step that we can paste into our Azure function. This URL won’t appear until we save the flow, and to save the flow we need to have both a trigger and an action.

4. Click New Step, Add an action.
5. Add a sample action like Office 365 Outlook – Send an email. It doesn’t really matter what you add here, since we’ll be deleting it once we have the URL for the HTTP request.
6. Click Create Flow.
7. Copy the HTTP POST URL from your Request step. Keep this URL safe, since it can be used by anyone to trigger your flow. Then delete the Send an email step.
8. Switch back to your Azure Function and update the Invoke-RestMethod command with the URL, then click Save.
9. Return to your Microsoft Flow, Click +New Step, then Add an action
10. The first thing we’re going to do is convert Proxy Addresses from an array to a string. To do this, we’ll use the Data Operations – Compose action. Click New Step, and add an action. Type ‘compose’, and choose Data Operations – Compose.
11. Choose ProxyAddresses from the dynamic content menu.
12. Since Body contains a collection of users, an Apply to each step will be created for you. This will allow us to iterate through the list.
13. Rename the Compose action to something descriptive by clicking the … menu and selecting Rename.
14. Click Add an action and add a SharePoint – Get Items action. Then select the SharePoint site and list that we created earlier.In this case, we’ll be filtering by ObjectId in the Sharepoint list to determine whether a user has already been added.Use the following filter expression to retrieve the item where ObjectId equals the Object Id of the current user:

    ObjectId eq 'ObjectId'

    Replace the second ObjectId with the ObjectId item from the HTTP Request:

No nested for each loops in Microsoft Flow

Now’s a good time to mention that Microsoft flow doesn’t allow us to create loops within loops. Since we’re already in an Apply to each (for each) loop in this Microsoft flow, we can’t go any deeper. Unfortunately at this point we’ve just potentially retrieved a SharePoint item that matches our Object ID as an array. To check the contents of an array, we’d usually need to loop through it, even though this one will only ever have a single item.

So to check the contents of this array, we need to convert it to a string first, then see whether the string contains the user’s ObjectId. There may be a better way, though I couldn’t find one.

18. Create a Data Operations – Compose action, rename it to ConvertString and enter a ‘.’ character followed by the value. It doesn’t have to be a period, it could be pretty much any character. We’ll compare our current Object ID against the output of this step.
19. This step will check whether the user exists. Create a Condition and enter the output from the ConvertString step into the first field, choose contains from the dropdown, and add in the ObjectId from the HTTP request.
20. At this point, your flow should look something like this.

If the user details have been added, update the existing SharePoint item

What follows here is something I’m not very proud of. I’m new to string or JSON manipulation within Microsoft Flow or Azure Logic Apps. The following steps work, though if you have a more elegant way to retrieve the ID from the returned JSON array without doing a nested for each loop, I’d love to hear it.

The reason we’re doing all of this String manipulation is because we can’t just use the ID field retrieved in the Get Items step since Flow expects it to be in an array. The following steps extract the ID from the JSON object returned by SharePoint – Get Items

Since Microsoft Flow cleans up after you save, the following screenshots contain values that aren’t surrounded by double quotes, and don’t have escaped characters – eg \”. You’ll need to enter the text above the screenshots for these steps to work.

21. Add six Data Operation – Compose actions.
22. Rename the first Data Operations – Compose action in the Yes case to SplitByQuotes. This retrieves the ConvertString output from the earlier step and converts it into an array separated by the character: ”
    

    "@split(outputs('ConvertString'),'\"')"

    

23. Rename the second Data Operations – Compose action in the Yes case to RemoveAfterID. The output of the SplitByQuotes action places the SharePoint Item ID (InternalItemId) in the 10th item of the array. This step removes all following items.

    "@take(outputs('SplitByQuotes'),10)"

    

24. Rename the third Data Operations – Compose action in the Yes case to GetID. This step skips the previous 9 items in the new array and retrieves the item containing the SharePoint InternalItemID that we can use to update the relevant SharePoint item.

    "@skip(outputs('RemoveAfterID'),9)"

    

25. Rename the fourth Data Operations – Compose action in the Yes case to ConvertIdToString. To use a replace string function in the following step, we convert the item to a string again by adding a ‘.’ to the start of it.
26. Rename the fifth Data Operations – Compose action in the Yes case to RemoveStart. This step removes the .[” from the start of the string and replaces it with nothing

    "@replace(outputs('ConvertIdToString'),'.[\"','')"

    

27. Rename the sixth Data Operations – Compose action in the Yes case to RemoveEnd. This step removes the “] from the end of the string and replaces it with nothing.

    "@replace(outputs('RemoveStart'),'\"]','')"

    

28. You can now add the RemoveEnd output into the ID field of a SharePoint – Update item action, along with all the other relevant data.
29. Be sure to use the modified values where applicable, these include:
    – LicenseString
    – LastPasswordChangeTimestampInvarient
    – WhenCreatedInvarient
    – The output of the ‘Convert Proxy Addresses to String’ action
30. Add a SharePoint – Create item action to the No case with the following values:
31. Your complete Microsoft Flow should look like this:
32. If you haven’t tried it already, you can test your Microsoft Flow by switching back to your Azure Function and running it.

Monitoring your Microsoft Flows

See the status and run history of your Microsoft flow by clicking the information icon on the Flow under My flows.

You can click on a particular Flow to get more information.

In-progress flows will show what step they are up to, and how long each action has taken.

Once the flow has completed, you can use the filtering features in SharePoint to gain insights on your user data.

In our case, we wanted to report on the following:

• Which licensed users across all tenants have blocked credentials
• Which licensed users are shared mailboxes
• Which passwords were last reset a long time ago

Next, we’ll set up an Azure Function that we can use to automatically create Exchange Administrators in all delegated tenants. We’ll block the credentials for these users by default, as we’ll only be using them to perform Exchange tasks that aren’t possible via Delegated Administration.

In the last blog post we’ll set up an automated user offboarding script with an approval step that can be initiated from SharePoint. This Microsoft Flow/Azure functions combination will automatically complete the following 5 offboarding actions:

Convert to a Shared Mailbox

The user will be converted to a Shared Mailbox to retain their email data

Disable ActiveSync

The user will be disconnected from their email account on all mobile devices

Remove Licenses

The user will lose all of their current licenses

Block Credentials

The user will be blocked from signing in to Office 365 services

Change Password

To speed up the disconnection process, the user’s password will be changed