Posted in : Active Directory, Azure, Azure, Microsoft, Office 365, Powershell, Uncategorized, Windows By Johan Nilsson Translate with Google ⟶

1 year ago

DISCLAIMER: This solution is NOT fully optimized. We put a WORKING solution together and have yet to ”clean up”.We cannot recommend that this will work for everyone.
It is recommend that you have basic knowledge in Powershell, Function Apps and pipelines (either Synapse or Data Factory) before applying this solution. 

A while back we got a request to export every user from the Azure Active Directory and a wish to have it presented through a Power BI report. As I have a long history with Powershell, it was my instant ”go-to” tool. A solution was put in place with a Powershell script running on an Azure Runbook, using a Managed Identity to generate a Graph Token, extracting the data to a CosmosDB. The only problem with this fully automated solution was the limitations within Azure Runbook. Either it’s 3 hours before it times out, or the if job requires more than 400MB memory. The solution was up and running, but only succeeded 1 out of 10 times, and the other 9 out of 10 times it either took longer than 3 hours or the memory cap was hit. This solution was up and running for a few months before me and my team started using Azure Synapse Analytics and saw the endless possibilities it had to offer. So we found another, fully automated (and safe) way to extract M365 users using Synapse Pipelines, Managed Identitiy and Function App

I did a bit of research, and the only information I could find was that people had been using Azure Data Factory to gather information from the Azure AD. Either using a credentials, secret or certificate. This is from my standpoint not an ideal solution since I am a big proponent of using Managed Identities to generate a Microsoft Graph token with the least amount of privileges. It had to be a better way.

Having a background using a lot of Powershell and working with Microsoft Graph API, I had an understanding that a Graph token could be generated through a Runbook, but there is not an easy way to connect the output from a Runbook to Synapse Pipelines. So I knew what I wanted, I just had to find the right way.

After clicking around in Synapse for a while, Azure Function App was by far the best option that I could come up with, so I had to try. So I created a Function App, enabled an Identity, and gave only the necessary privileges through some Powershell Code.  Necessary permissions can be found here regarding Microsoft permission references.

After setting the permissions, a function has to be created. I named mine ”GraphToken” which looks like the image below. In the right side of the options in the image you can also copy the URL which will be needed in Synapse Pipelines.

The next step was to create a Synapse Pipeline that generates a token which could be used through the workflow.

I will not go in details about the ”Send email on failure” in this blog post, but a short description is that when a task fails, a pipeline is triggered that sends an email to declared email addresses with information about what pipeline that has failed.
The whole flow looks like this:

NOTE: The only RBAC permissions needed on the Synapse Analytics Managed Identity is ”Storage Blob Data Contributor”. 

The sink files from the data flow looks like this:


The final file exported from sink3 M365_Assigned_Licenses_PROD



The exported file from sink1 M365_Licensed_Users_Prod

The last step is to map all the SkuID’s to readable name. This is done with a CSV file downloaded from a web request to How this is done can be found here

Since I have a great team of colleagues doing some Power BI magic they were able to fit this together. The customer had the possibility to search and filter through their whole Azure AD from this report, without having to login to the Azure Portal.

As show in this image; on the left side, all the licenses in the environment are shown. If you click on a license, the user(s) on the right with the selected license will be highlighted. And on the right side, you can expand a user and see all the products connected to that user.

We as a team has put over 200 hours developing the this solution. It maybe has some flaws, it may not be optimized in every single way, but this is for us, and i hope it will be for other companies a really good solution for this case.

If you have any questions, suggestions how we can improve this solution, or just want to say hello, feel free to email me at – or maybe if you are interested in having my team setting up this solution for you, send me an email! 

Tags : ADF, API, Function App, Graph, Graph API, Licenses, M365, Office 365, PowerBI, PowerShell, Synapse, Synapse Pipeline

Personlig rådgivning

Vi erbjuder personlig rådgivning med författaren för 1400 SEK per timme. Anmäl ditt intresse i här så återkommer vi så snart vi kan.

Add comment

Your comment will be revised by the site if needed.