Intune and PowerBI Deep Dive - Part 5 - Heavy Lifting

In this post we’ll take a look at using Logic Apps and OData.Feed to do the ‘heavy lifting’ for us. By heavy lifting, essentially this is handling pagination of the Microsoft Graph Call. Using the app registration permissions for authorisation to the data, then processing the result in order to make sure we have all the data we need and finally passing the results back to PowerBI so that its handled outside of PowerBI. PowerBI only cares about the data.

Quick Links

There are three ways I’ve achieved this. The first method passes multiple properties to a Logic App. The Logic app uses these properties to authorise against the tenant and do the work. The second method uses the function we created in a previous post to obtain a Bearer token for access and passes this token instead, replacing the need to pass individual variables. The final method is similar to the second in that it also uses the bearer token for authorisation, however, instead of using a Logic App we use an OData.Feed power query. The third method shares similarities with the second in that they both utilise a Bearer token for authorization. OData.Feeds support pagination so this removed the output processing element of our queries where we must convert or translate the results from the Logic App. There is no right or wrong approach, I would suggest examining them all in the following post and deciding which one suite your needs best.

Method 1 - Logic Apps & Passing variables

Previously we gathered the TenantID, the AppID and the SecretID after creating an application registration in Entra. Those details were stored into variables inside PowerBI, so we can call them using a Power Query. Lets first dive into the Logic App and start configuring it to recieve these properties. Luckily this is quite simple. Just three steps. A hat tip to Jan Baker who first wrote this up in 2020. Jan sets some parameters in the logic app instead of passing them in from PowerBI. Passing them in from PowerBI allows me to change them if I need to, on the fly with different environments however if you didnt want to pass them over, you could hard set them in your Logic app and omit the need to pass in variables using a JSON configuration.

When you are passing details up to a Logic App it needs to be told what its receiving and what to to do with the data its passed.

Lets go ahead and configure this logic app to receive what it needs. We will need to consider five things:

• TenantID (Already in a variable)
• AppID (Already in a variable)
• SecretID (Already in a variable)
• Audience, in our case this is going to be https://graph.microsoft.com/, we will pass this in from our Power Query. This is taken from the Endpoint variable, one of the four elements used to Build the full GraphURL.
• and finally our fully built Microsoft Graph URL, knows as GraphURL (this is a combination of Endpoint, Version, Resource and Query Parameters)

Our logic app contains just three steps:

1. When a HTTP request is received
2. A standard HTTP step (this is the step we authorise with and the step that will handles pagination)
3. A response step to pass the data back.

On the first step of the Logic App, we must configure it to receive the five variables we specified above. We do this by configuring a JSON with five properties. Those properties are all string properties and we’re going to pass in the string variables we have created in PowerBI.

The JSON Request body in the image above looks like this, when expanded:

{
    "properties": {
        "AppID": {
            "type": "string"
        },
        "Audience": {
            "type": "string"
        },
        "GraphUrl": {
            "type": "string"
        },
        "SecretID": {
            "type": "string"
        },
        "TenantID": {
            "type": "string"
        }
    },
    "type": "object"
}

Breaking this down we have a AppID (String), Audience (String) remember this is going to be the Endpoint section of the Graph URL (see part 3) so we’ll already have this in a variable as we build out our Power Query, the GraphURL itself (String) made up of the Endpoint, Version, Resource and Query Parameters, the SecretID (String, see Part 2) and finally the TenantID (String).

So whats this look like in Power Query passed in from PowerBI? In the Logic App section of the query below example, the name of the property is on the left and the value in on the right. It is expected that we will set the “Audience” and “GraphUrl” on each query, and the remaining three are PowerBI variables, so global and can be called in any query.

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/managedDevices/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,  

// Logic App to handle pagination
    LABody = "{
                ""GraphUrl"":""" & GraphURL & """, 
                ""TenantID"":""" & TenantID & """,
                ""AppID"":""" & AppID & """,
                ""SecretID"":""" & SecretID & """,
                ""Audience"":""" & Endpoint & """
            }",

If you notice we’ve escaped the " character so the format is correct. When checking this in the PowerBI steps we can see its formatted right. The Property name in speech marks, followed by a colon and then the property value in speech marks. Precisely what is expected to be passed.

We must then pass this JSON body to the Logic App using Web.Contents, we will take the value of this output as our result

    LogicApp = Json.Document(Web.Contents(#"MSGraphCall-NoBearer", [Headers=[#"Content-Type"="application/json"],Content = Text.ToBinary(LABody)])),
    Value = LogicApp[value],

Notice we call the URL stored in our variable MSGraphCall-NoBearer and pass in the Logic App JSON Body (LABody). Clever stuff, right?

The second step in the logic app takes the input parameters from step one to configure the step in order to authenticate with your tenant and retrieve the data. Remember, this access is granted by the permissions set on the app registration created in step 2

We must also set the pagination on the settings of the step

And finally the body of the step is passed back.

Method 2 - Logic Apps & Passing a Bearer Token

This method requires us to use PowerBI to request the bearer token using the function created in part 4 and only pass that to the Logic App along with the Graph URL you want to call. I have noticed that the SecretID passed is masked by default in the logic app however, if you feel more comfortable passing a bearer token up to Entra rather than all those variables, this is the method for you.

So following the same logic as above, lets take a look at the same three steps, edited to process the same data but this time with a bearer token.

Again lets configure this logic app to receive what it needs. This time, only two strings needed:

• GraphUrl (Already in a variable)
• Bearer (Already in a variable)

In the first step, we must configure the JSON Body.

The JSON Request body looks like this:

{
    "properties": {
        "GraphUrl": {
            "type": "string"
        },
        "Bearer": {
            "type": "string"
        }
    },
    "type": "object"
}

This will take a similar, but shorter form in Power Query.. like so:

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/managedDevices/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,   

// Get Bearer Token Using Function 
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint),

// Logic App to handle pagination
    LABody = "{
                ""GraphUrl"":""" & GraphURL & """, 
                ""Bearer"":""" & Bearer & """
            }",

In this method, we call the Get-BearerToken function in PowerQuery first, passing in four parameters (Endpoint is set to “https://graph.microsoft.com/") then we take our fully build Microsoft Graph URL and our Bearer Token and pass these to the logic app using Web.Contents

    LogicApp = Json.Document(Web.Contents(#"MSGraphCall", [Headers=[#"Content-Type"="application/json"],Content = Text.ToBinary(LABody)])),
    Value = LogicApp[value],

The second step of the logic app we must configure like this:

And like the example above, we pass back the results

Logic App Benchmarking

I have conducted performance benchmarks on these Logic app, gathering data from environments containing upwards of 2-3000 clients. The Logic app consistently achieves execution times of just a few seconds. Even in scenarios with larger datasets, such as 4000-5000 clients, the execution times remain rapid. This efficiency is primarily attributed to both using list calls from Graph, which retrieve the majority of relevant information efficiently and the simplicity of the logic app. While individual calls may potentially yield slightly more data, in my experience, this approach is generally unnecessary. List calls have consistently provided all necessary data. When leveraging proper table relationships and ensuring standardised queries and datasets, the scalability of generating reports significantly improves. Below are some performance results for various Graph calls across different environments.

No Bearer Token	Bearer Token

Benchmarking results for illustration only.

Method 3 - OData.Feeds & Passing a Bearer Token

This method requires us to use PowerBI to request the bearer token using the function created in part 4 and pass this to an OData.Feed Power Query. Remember, OData.Feed supports pagination so all you need to do is pass whats required and the rest is handled for you. OData.Feeds are free to use. This might be important to you if you do not wish to incur the costs of a Logic App. They are not without issues though. Most recently (Jan -2024) Microsoft changed things with the Beta version output for the Graph Resource deviceManagement/managedDevices/ resulting in the following error.

Because the feed is broken from the off - those of you new to this, wont get any results from a blank call. This means you wont be able to tell which columns you want to keep or exclude using something like a filter. Instead, I flipped to v1.0 of Microsoft Graph because the results still work, this could help with column selection for a filter. I will be raising this with Microsoft and dropping back in here if anything changes.

With OData.Feed, there is no Logic App to throw anything out to, so this is all done inside Power Query. See the below structure I used to get my results. Be aware that the default timeout for an OData.Feed is 600 seconds, or 10 Minutes.

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "v1.0/",
    Resource = "deviceManagement/managedDevices/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

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

// ODataFeed to process Graph Call
// https://learn.microsoft.com/en-us/powerquery-m/odata-feed
    OData = OData.Feed (
        GraphURL, 
            [ Authorization = Bearer ],
            [
                ExcludedFromCacheKey = {"Authorization"},
                ODataVersion = 4,
                Implementation = "2.0",
                OmitValues =  ODataOmitValues.Nulls
            ]
    ),

• Section 1 - We build the GraphURL variable
• Section 2 - We use the Get-BearerToken function to get a Bearer token and store it in the Bearer variable
• Section 3 - We use OData.Feed, Pass in the GraphURL alongside passing the Bearer in for Authorisation.

The results pass back our table, no need to process the output so we can delve straight into formatting the table data, one way or another.

Conclusion

If you chose Logic Apps, your choice of method will dictate the approach for creating queries in PowerBI. Consider the following :

• Are you dealing with a dataset exceeding 100,000 devices, users, or groups? If so, consider the limitation of 100,00 records and consider that you are going to need to do further work on the logic app.
• Ensure you have the URL from the Logic App step “When a HTTP request is received” (Step 1) so you can invoke the Logic App using this.
• Consider the cost involved.

If you chose to use OData.Feed, familiarise yourselves with the proprties available to you and keep an eye on performance. You may have to add further properties to cope with your environment.

Do not concern yourself with the fulll PowerBI Query just yet. This post focuses on deciding on your chosen method. Subsequent posts will delve into all three methods further, ensuring comprehensive coverage and support throughout the setup process and providing queries you can use. I will store the queries on my Github page and once I have enough queries we’ll look to formaing a report template together for the series.

How subsequent posts will be formed

In the next few blog posts I’m going to share with you some queries that you can steal with pride. I will typically break down each subsequent post as follows:

• The Graph URL
• Global Pre-Requisites (Must be done regardless of method)
  
  Under your chosen tab
  • Pre-Requisites (for your chosen method)
  • Power Query - The full query code (including a Github download link)
• Creating the Query (How to place this into PowerBI)
• Explanation
• Relationships (to other tables - these sections will be updated as the series goes on)

Navigate the tabs on each post and then continue reading down the page, like this. Your tab choice will be forgotten if you refresh the page make sure you’re reading the correct method.

This series is intended to bring you along for a journey and give you a good working knowledge of how to achieve visualising your Intune data. With that said, the subsequent posts can be used individually as I intend to work through a few Microsoft Graph resources, some of which may not be important to you or your business when you build out your own reports.

In the Part 6 I will demonstrate how to form your Power Queries using the three methods, allowing you to choose the one that best suits your needs and circumstances. These queries will be hosted on my Github for you to go and use yourself. Work through the posts one by one and we should have a good data set built by the end of the series.

Links and References