Cmdlet in a Box: Get All Users in Azure Tenant with Power Automate and MS Graph Connector

Overview

"Finally an easy way to get a list of all users in my tenant," said an Azure admin NEVER. Then this connector popped up.

A use-case called for the creation of a user list across an Azure tenant, a rolodex of sorts, that would display all users to in an onboarding app and report. The report would be refreshed daily.

We know where the info for a list is stored...but how do we get it out and put it into a format suitable for downstream use?

?The MS Graph Groups and Users connector: a cmdlet in a box.

The List Users action is designed to return all users in the Azure tenant, similar to the PowerShell Get-AzureADUser cmdlet. What's more, the results are returned in JSON so they're ready to use elsewhere and for other things.

This article will show you how to populate a SharePoint list with data pulled from the MS Graph Groups and Users connector.

Caveats

Some things to know before you go.

• Connector in Preview. As of this writing, the MS Graph Groups and Users connector is still in preview (12/22). Some key features will be added so consider this article a preview as well.
• Wholesale! This is the only OOTB connector that will return all users at once; a custom connector or HTTP connector are the only other options. Office 365 returns max 999 results at one time. The Azure AD connector does something similar but it's more involved.
• Results are tenant-wide. Every user in the tenant will be returned, human or not. The action casts a wide net so consider using the Filter Array action to exclude results, which will produce results in the same manner as the Get-ADUser cmdlet.

Not too bad, right?

Prerequisites

The following must be in place for this to work as intended.

• Configure Data Loss Prevention (DLP) policy. The data loss prevention policy in your environment must be Graph connector-friendly.
• SharePoint list required. A SharePoint list is required to land the data. We can also use the Dataverse or SQL Server. SharePoint is just for demonstration. Create a list and give it a name, PhoneNumberDirectory. Use these fields and their type for the list:

Name – Single line of text

BusinessPhones – Single or multiple lines of text

Display Name – Single line of text

Given Name – Single line of text

ID – Single line of text

Job Title – Single line of text

Mail – Single line of text

Mobile Phone – Single line of text

Office Location – Single line of text

Preferred Language – Single line of text

Surname – Single line of text

User Principal Name – Single line of text

Value – Single line of text

The purpose of these fields should be self-explanatory.

• Premium connector. The MS Graph Groups and Users connector is a Premium connector in Power Automate (but of Standard class in Azure Logic Apps, different story). Proper licensure required.
• ?Read more about the MS Graph Groups and Users connector here .

Steps

Here are the basic steps to automate pulling a tenant-wide user list.

Let's see how it's done.

Set Run Schedule

Our requirement calls for a daily refresh so add a Set Recurrence for our initial action.

Set the time and the time zone as well.

List Users from MS Graph

Add the MS Graph People and Groups action.

Great news: you don't even have to do anything.

Details included are exactly what the SharePoint list will hold. The results are already in JSON, which will provide an array straight away to place into the destination SharePoint list. Juicy.

Initialize and Set Array Variable

If all is well, add a Set Variable action and store the user list details from Graph into our array variable.

Add the value from the Dynamic Content window.

If you would like to use an expression:

outputs('Action_Name_With_Spaces')?['body/value']        

Parse JSON (Optional)

We do not need to add a Parse JSON action, as the output from the List Users action is already in JSON, but here is the schema for reference.

{
? ? "type": "array",
? ? "properties": {
? ? ? ? "businessPhones": {
? ? ? ? ? ? "type": "array",
? ? ? ? ? ? "items": [
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? "type": "string"
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ]
? ? ? ? },
? ? ? ? "displayName": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "givenName": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "jobTitle": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "mail": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "officeLocation": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "surname": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "userPrincipalName": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
	 "mobilePhone": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
	 "preferredLanguage": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
	 "value": {
? ? ? ? ? ? "type": "string"
? ? ? ? },
? ? ? ? "id": {
? ? ? ? ? ? "type": "string"
? ? ? ? }
? ? }
}        

Add a Parse JSON action and paste the schema in the Schema area.

Important to note that business phone numbers is an array field of string type. If multiple phone numbers exist for a user then they will all fit. Further, if you have Phones for Teams, businessPhones will include the assigned Teams number(s). As this is an array, multiple values may be available from various sources (Outlook as well as Teams).

The stage is set to populate our SharePoint list.

Set SharePoint List

Before we do that, however, we need to establish our SharePoint list. Add a Get Items action using the SharePoint connector.

Replace the Site Address and List Name items with your list details.

Apply to Each – Truncate and Load List

"Erase the slate," spake Dokken. This step will delete all information from the SharePoint list. Useful for staging situations, a loop over the list will truncate the existing data.

Add an Apply to Each control. Then, add a Delete an item within the Apply to Each loop.

Set the output to value via the Dynamic Content window. This is our SharePoint list. Pop open the Delete an item action using the SharePoint connector again and specify the site, list name and ID for each record to be deleted.

The ID value is available in the Dynamic Content window within our Set SharePoint List action.

Lastly, we populate the SharePoint list with the Graph results. Add another, separate Apply to Each control to create new records and insert a SharePoint Create an item action within the loop.

Add the UserDetailList array variable to the Select an output... field.

We must map the fields from the array to a landing spot in the SharePoint list. Add this expression to each field:

items('Action_Name')?['field_name']        

Naturally, replace the action name with the appropriate name of the action that you have used as well as the field names from Graph.

Naturally, replace the action name with the appropriate name of the action that you have used as well as the field names from Graph.

Refresh Power BI Dataset

You can do anything here from here. Power BI is merely a suggestion. If you use the list as a data source in a report (which is what we do), the dataset can be refreshed. Slick.

Be mindful of the refresh limits. If you are refreshing the dataset via the cloud service then 8 refreshes/day max for Pro license, 48/day for Premium. This is not the case if you use an alternative such as Power Automate or the REST API.

Our final flow:

Und so geht das. Straightforward solution for a common request. At first, I wanted to include a condition that handles errors but we will leave that for another lesson.

Questions about the connector? Have a similar approach using a different method? Get in touch!

Resources

• MS Graph Groups and Users (Preview) – MS Learn
• Get-ADUsers – MS Learn