Retrieve Azure AAD user information with Azure Functions and publish it into PowerBI

Few days ago I had a requirement to retrieve user information from Azure Active Directory and publish the data into a Power BI dashboard.

In PowerBI you can’t directly query Azure Active Directory (there is, however, a connector to query an on premise Active Directory environment) so I had a quick chat with my good friend and CDM MVP Tao Yang about how this could be achieved. After a few brainstorm sessions he suggested to try Azure Functions to retrieve the user information from Azure AAD.

In this blog post I will show you how to build an Azure Function to connect to Azure AAD and publish the data to PowerBI. Tao has written another blogpost about how to retrieve information from Azure VMs and use that in Power BI, so make sure to have a look at his blogpost as well.

Requirements

You will need to following components:

• Azure Subscription
• Azure Function (which we are going to build in this blogpost)
• MSOnline PowerShell Module (to connect and retrieve the Azure AAD users)
• Power BI online account
• Power BI desktop

Build new Azure Functions to retrieve Azure AAD information

In the Azure portal create a new Azure Function app and give it a name. Make sure to select Dynamic as the App Service Plan so you only pay for what you consume.

You will find your new Azure Function in App services after the deployment has completed.

 

Upload the MSonline PowerShell Module in Azure Functions

To be able to read the Active Directory user data from Azure AAD you will need the MSOnline PowerShell module installed into your Azure Function. Firstly you will need to download the MSOnline PowerShell module locally to your computer and then upload it your Azure Function.

save-module msonline -repository PSGallery -Path "C:\temp"

Once you have the module you need to upload it to Azure Functions.  I’m not going to cover how to import customer PowerShell modules into Azure functions in this post as Tao has already covered that. Make sure to follow Tao’s blog or your Azure function will fail.

Now you are ready to start using your first Azure Function!

Configure your Azure Function

To be able to authenticate against our Azure AAD environment we will need to provide some credentials. I’m not going to describe in this blogpost how to handle secrets in Azure functions as Tao Yang and David O’brien have already covered how to do that.

Click on Configure App Settings and change the following settings:

• Change the platform from 32-bit to 64-bit
• Add a new App Setting called Password with the encrypted password of our Office 365 user. We will retrieve this custom app setting later on in our PowerShell script
• Add a new App Setting called User with the username of our Office 365 user. We will retrieve this custom app setting later on in our PowerShell script

Click on Save.

Create a new Azure function

Select New Function, change the language to PowerShell and select Empty – PowerShell. Give it a name and click Create

Now that we have our Azure Function up and running and configured we need to retrieve the Azure AD data. The following PowerShell script will connect to you Azure AAD environment with the provided credentials and retrieve Azure AD user information.

Copy the following code into your function:

$FunctionName = 'GetAADUsers'
$MaxResults = 2000
$ModuleName = 'MSOnline'
$ModuleVersion = '1.0'
$username = $Env:User
$pw = $Env:Password
#import PS module
$PSModulePath = "D:\home\site\wwwroot\$FunctionName\bin\$ModuleName\$ModuleVersion\$ModuleName.psd1"
Import-module $PSModulePath

#credential
$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-MsolService -Credential $credential

$users = Get-MsolUser -MaxResults $MaxResults
$HTMLOutput = ($users | ConvertTo-Html -Title 'Azure AD Users' -Property DisplayName, FirstName, LastName, UserPrincipalName, LastPasswordChangeTimestamp, PhoneNumber, StreetAddress, City, State, Country, PostalCode, PasswordNeverExpires, IsLicensed) | out-string
Out-file -encoding Ascii -FilePath $res -InputObject $HTMLOutput

And, as you can see in the script above we are using the app settings we have created earlier as variables ($Env:User and $Env:Password)in our script.

We are now ready to test our Azure function. Copy the function URL at the top of the page and open PowerShell ISE.

Test GetAzureAAD function

To test and verify if the Azure function is working we just open PowerShell ISE and call our function URI:

#

After this we can verify our call was successful by viewing the content of our request:

$request.content

In your favourite browser it will look like this:

You can check for any errors or monitor the runtime of your Azure Function by clicking monitor in the Azure Portal.

As I discovered later on, you cannot use the output that we generate in our Azure Function in Power BI online. You can however use it in Power BI desktop, but not in Power BI online as you are unable to refresh the data and define a schedule refresh. This is because the content type of the output is not set correctly. I believe this is a PowerShell limitation in Azure Function (it’s still in preview) and a bug in Power BI. So Tao came to the rescue again and created another Azure Function in C# to wrap our PowerShell function and generate a proper output. You can read more about this on Tao’s blog.

Select New Function, change the language to C# and select HttpTrigger-C#. Give it a name and click Create

In your function replace the existing code with the following code from Tao:

As you can see we are creating a C# wrapper that need a requesturl (which is our URI from our first function). Click on Save

Now open your browser and copy paste the url from the C# Azure function and append the parameter requesturl behind the URI with the URI of the first Azure Function. So this HTTPTriggerProxy function calls the other url that you specified.

The output looks a lot nice now:

Now that we have our data that we want we can retrieve that data and use it in Power BI.

Retrieve Azure function data into Power BI

To retrieve the data from our Azure Function in PowerBI, click on Get Data in Power BI desktop and select Web

In the next window provide the function URL you copied earlier. The basic option is enough for the purpose of this demo, in the advanced mode you can define HTTP request headers and command timeouts which we don’t need now.

Select Table 0 in the next window and click Load. You will now be able to use the data from your Azure function in Power BI. Below you can see a quick example of my AD dashboard that gives me a quick overview of unlicensed users, location of the users, etc. I’m not going to go into detail on the different Power BI visuals, but below you can find an example of my Azure AD Power BI dashboard.

That’s it! How awesome is this? Let’s publish our dashboard now to Power BI online.

Publish dashboard to Power BI online

Once you are done creating your Power BI dashboard you can publish it to Power BI online and share it with others. In Power BI desktop, click Publish

Once published select Open in Power BI

To refresh the data automatically click on your new Dataset and select Schedule Refresh

Configure your dataset as seen below:

click on edit credentials and make sure the authentication method is set to Anonymous

Conclusion

Using Azure Functions to retrieve data enables a whole new world of capabilities to retrieve and pull data into PowerBI from data sources that are not natively supported by PowerBI!

Are you missing any data sources in Power BI today? What data sources would you like to query that is not natively supported by Power BI?

Thank you,

Alexandre