Intune and PowerBI Deep Dive - Part 4 - Get-BearerToken

In the previous posts, we talked about obtaining an access token (bearer token) to access the data in our environments. We even looked at using our first POST query to post a request for a token in PostMan. The reason I’ve called this post ‘Get-BearerToken’ is because I’m going to explain how to use a combination of PowerBI Variables and a function to leverage the Application Registration we have to get a bearer token within PowerBI Desktop. We can then use that bearer token to authenticate against the Microsoft Graph and start to pull data into PowerBI like we did in the PostMan application.. If you didn’t know, a Graph response will provide a maximum of 1000 replies and the rest of the replies will be split into chunks, known as pages. So page 1 contains responses 1-1000, page 2 1001-2000 and so on and so forth. If we don’t put this response into some sort of loop, to get extra responses we will only ever see part of our data. This is known as pagination . it is a consideration for the next few posts.

Quick Links

Concept

Here is my a visualisation of where we are so far in this journey

• We have three discovery tools at hand to collect data from Intune for our PowerBI report. Microsoft Graph Explorer is our first port of call to get used to the way things are formatted, get used to adding permissions, and formulating a query. Graph Xray lets us see what graph urls are needed to see the same data in the Intune console as we browse around, it gives us a great starting block for building up what we want to put into our PowerBI report. And finally Postman, a API tool that we set up to use on our environment, giving us the ability to quickly prod the Graph, test calls and see the data after we move on from Graph Explorer. Remember that Postman will leverage the Application Registration we have in Azure (which inturn uses the Graph) to go get data so isn’t tied to a user. This is what we want to use.
• Our application Registration sits in Azure and acts as the go-between communicating with the Microsoft Graph on our behalf as long as we provide the key authentication elements needed. These are the TenantID, AppID, SecretID. We can use these to get an access token to give us access to the data.

What we want to do now is get PowerBI to use the App Registration to do the same things

So lets give that a go!

Firstly lets create 3 variables in PowerBI Desktop. Open PowerBI desktop, and select to Transform Data. From this window, right click and select New Parameter. Create the following three:

• TenantID (Text)
• AppID (Text)
• SecretID (Text)

You should have these values from creating your app registration in Part 2 .

This way we can change the variables if we need to and also reference them in our queries.

Create a new Group called Parameters and place the three parameters within it.

Next lets create a blank query in PowerBI, then go into advanced editor and paste in the following code (Notice this code references the variables which must be created and populated to work)

  let
    Source = (TenantID as text, AppID as text, SecretID as text, Resource as text) => let

        // Get an Access Token to make Graph Calls (uses Application Registration)
        ClientId = Text.Combine({"client_id",AppID}, "="),
        ClientSecret = Text.Combine({"client_secret", Uri.EscapeDataString(SecretID)}, "="),
        GrantType = Text.Combine({"grant_type", "client_credentials"}, "="),
        Resource = Text.Combine({"resource", Resource}, "="),
        
        Body = Text.Combine({Resource, ClientId, ClientSecret, GrantType}, "&"),
    
        AuthResponse = Json.Document(Web.Contents(
               "https://login.microsoftonline.com/",
            [
                RelativePath = Text.Combine({TenantID,"/oauth2/token"}),
                Content=Text.ToBinary(Body)
            ]
        )),
    
        AccessToken= AuthResponse[access_token],
        Bearer = Text.Combine({"Bearer", AccessToken}, " ")
    in
        Bearer
in
    Source

Save the query. Create a new Group called Functions and place the Function within it. Rename the function to Get-BearerToken, you may notice the icon next to the query is fx like this… …representing a function rather than a query and in order to call this function you must specify the 4 parameters of:

• TenantID
• AppID
• Secret
• Resource (this represents what you want to authenticate against)

Now we’ll need to build a query to call this function and return a bearer token for us to use for a graph call. Lets create a new query and place the following code at the top of it

let
    // Microsoft Graph URL
    Resource = "https://graph.microsoft.com/",   

    // Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Resource)
in
    Bearer

The above query, uses the resource of https://graph.microsoft.com/ when attempting to get a bearer token. The attempt uses the function and, as you will see, it does indeed return a bearer token.

EXCELLENT! Now we have code to call for a token, the token is what we need to authenticate against the Graph and bring into PowerBI data from Intune (or any other resource).

Now, in the Part 5 we’ll examine using Logic Apps along side this token to get some data and return it back to the PowerBI desktop app.

Links and References