Intune and PowerBI Deep Dive - Part 8 - Autopilot Devices

post-thumb

In this post we’ll look at pulling in details about Autopilot devices. Our list call from Managed Devices, covered in part 6, gives us a column to tell us whether that device is autopilot enrolled but what if we want more information than that? What if we want to know the group tag it uses or whether it has an autopilot profile assigned to it. Lets go get that!

Table Of Contents

Graph URL

The graph URL we are using in this section is deviceManagement/windowsAutopilotDeviceIdentities/.
Here is the documentation links;

Global Pre-requisites

Note

Below are three tabs, for three different types of queries. You will only need to chose one of these three methods. Select the tab for the method you wish to follow then continue to read the post.

Chose your method

  • Web.Contents (No Bearer)
  • Web.Contents (with Bearer)
  • OData.Feed (with Bearer)
Web.Contents using a Logic App with no Bearer Token

Warning

There is a cost implication for using Logic Apps, please investigate Logic App Pricing to ensure you are comfortable with this.

In this query we are going to leverage Web.Contents to call the logic app that does not require a bearer token and instead pass in the parameters TenantID, AppID and SecretID. We are also going to build two variables called in our query called GraphURL and Audience. The GraphURL we create, is made up of 4 parts. The Audience variable is going to contain one of the four parts, namely the Endpoint part. We will need the logic app call URL storing in a Parameter called MSGraphCall-NoBearer. This is the URL we will pass the parameters to and then the logic app will process the call and pass the data back to PowerBI so we can transform the data in order to form a report.

Pre-Requisites
  • You have created the Logic App from this post described in Method 1, which will handle pagination of the Microsoft Graph call for you.
  • You will need to create a new PowerBI Parameter called MSGraphCall-NoBearer and in it, place the HTTP POST URL from the “When a HTTP request is received” step of your Logic App.
Power Query
let

// Application Registration Permissions needed to make this call : DeviceManagementServiceConfig.Read.All
//                                                               : DeviceManagementConfiguration.Read.All

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

// Logic App to handle pagination
    LABody = "{
                ""GraphUrl"":""" & GraphURL & """, 
                ""TenantID"":""" & TenantID & """,
                ""AppID"":""" & AppID & """,
                ""SecretID"":""" & SecretID & """,
                ""Audience"":""" & Endpoint & """
            }",
    LogicApp = Json.Document(Web.Contents(#"MSGraphCall-NoBearer", [Headers=[#"Content-Type"="application/json"],Content = Text.ToBinary(LABody)])),
    Value = LogicApp[value],

// Output Processing
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "deploymentProfileAssignmentStatus", "deploymentProfileAssignmentDetailedStatus", "deploymentProfileAssignedDateTime", "groupTag", "purchaseOrderIdentifier", "serialNumber", "productKey", "manufacturer", "model", "enrollmentState", "lastContactedDateTime", "addressableUserName", "userPrincipalName", "resourceName", "skuNumber", "systemFamily", "azureActiveDirectoryDeviceId", "azureAdDeviceId", "managedDeviceId", "displayName", "deviceAccountUpn", "deviceAccountPassword", "deviceFriendlyName", "remediationState", "remediationStateLastModifiedDateTime", "userlessEnrollmentStatus"}, {"id", "deploymentProfileAssignmentStatus", "deploymentProfileAssignmentDetailedStatus", "deploymentProfileAssignedDateTime", "groupTag", "purchaseOrderIdentifier", "serialNumber", "productKey", "manufacturer", "model", "enrollmentState", "lastContactedDateTime", "addressableUserName", "userPrincipalName", "resourceName", "skuNumber", "systemFamily", "azureActiveDirectoryDeviceId", "azureAdDeviceId", "managedDeviceId", "displayName", "deviceAccountUpn", "deviceAccountPassword", "deviceFriendlyName", "remediationState", "remediationStateLastModifiedDateTime", "userlessEnrollmentStatus"})

in
    #"Expanded Column1"

Query Structure
  • Firstly I’ve called out the permissions needed. A good practice I like to follow.
  • Then I’ve built out the properties for the Graph URL. This is made up of four elements Endpoint, Version, Resource and Query Parameters. Resulting in one final variable of GraphURL which contains all four put together.
  • Next we create the JSON body for the logic app call. This includes our five variables: TenantID, AppID, SecretID, GraphURL and Audience. Audience is simply the Endpoint part from building the previous GraphURL.
  • Then we pass the JSON Body to the URL for the Logic App stored in the variable MSGraphCall-NoBearer using Web.Contents.
  • Next, we process the output. This includes converting the list of records to a table and then expanding each of the records inside the table.
  • and BOOM! we have our data in PowerBI!
Web.Contents using a Logic App with Bearer Token

Warning

There is a cost implication for using Logic Apps, please investigate Logic App Pricing to ensure you are comfortable with this.

In this query we are going to leverage Web.Contents to call the logic app that does require a bearer token and pass in the parameters GraphURL and Bearer. The GraphURL we create, is made up of 4 parts. The bearer token is the result of calling the function we created in part 4. We will need the logic app call URL storing in a Parameter called MSGraphCall. This is the URL we will pass the parameters to and then the logic app will process the call and pass the data back to PowerBI so we can transform the data in order to form a report.

Pre-Requisites
  • You have created the Get-BearerToken function from this post.
  • You have created Logic App from this post described in Method 2, which will handle pagination of the Microsoft Graph call for you.
  • You will need to create a new PowerBI Parameter called MSGraphCall and in it, place the HTTP POST URL from the “When a HTTP request is received” step of your Logic App.
Power Query
let

// Application Registration Permissions needed to make this call : DeviceManagementServiceConfig.Read.All
//                                                               : DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/windowsAutopilotDeviceIdentities/",
    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 & """
            }",
    LogicApp = Json.Document(Web.Contents(#"MSGraphCall", [Headers=[#"Content-Type"="application/json"],Content = Text.ToBinary(LABody)])),
    Value = LogicApp[value],

// Output Processing
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "deploymentProfileAssignmentStatus", "deploymentProfileAssignmentDetailedStatus", "deploymentProfileAssignedDateTime", "groupTag", "purchaseOrderIdentifier", "serialNumber", "productKey", "manufacturer", "model", "enrollmentState", "lastContactedDateTime", "addressableUserName", "userPrincipalName", "resourceName", "skuNumber", "systemFamily", "azureActiveDirectoryDeviceId", "azureAdDeviceId", "managedDeviceId", "displayName", "deviceAccountUpn", "deviceAccountPassword", "deviceFriendlyName", "remediationState", "remediationStateLastModifiedDateTime", "userlessEnrollmentStatus"}, {"id", "deploymentProfileAssignmentStatus", "deploymentProfileAssignmentDetailedStatus", "deploymentProfileAssignedDateTime", "groupTag", "purchaseOrderIdentifier", "serialNumber", "productKey", "manufacturer", "model", "enrollmentState", "lastContactedDateTime", "addressableUserName", "userPrincipalName", "resourceName", "skuNumber", "systemFamily", "azureActiveDirectoryDeviceId", "azureAdDeviceId", "managedDeviceId", "displayName", "deviceAccountUpn", "deviceAccountPassword", "deviceFriendlyName", "remediationState", "remediationStateLastModifiedDateTime", "userlessEnrollmentStatus"})
in
    #"Expanded Column1"
Query Structure
  • Firstly I’ve called out the permissions needed. A good practice I like to follow.
  • Then I’ve built out the properties for the Graph URL. This is made up of four elements Endpoint, Version, Resource and Query Parameters. Resulting in one final variable of GraphURL which contains all four put together.
  • I then use the 3 PowerBI Variables we have TenantID, AppID and SecretID along with the Endpoint part of the GrpahURL to call the PowerBI function Get-BearerToken, and the return value is stored in the variable Bearer within the query code.
  • Next we create the JSON body for the logic app call. This includes our two variables: GraphURL and Bearer.
  • Then we pass the JSON Body to the URL for the Logic App stored in the variable MSGraphCall using Web.Contents.
  • Next, we process the output. This includes converting the list of records to a table and then expanding each of the records inside the table..
  • and BOOM! we have our data in PowerBI!
OData.Feed using a Bearer Token

Info

There is no cost implication for using OData.Feed.

In this query we are going to leverage OData.Feed v4 Power Query to call the Graph API. The OData.Feed support pagination, so the feeds itself will will process the call and then pass the data back to PowerBI (no Logic Apps!) so we can transform the data in order to form a report.

First we set up our GraphURL, as we have done with other queries building it with the four elements. Then we call the Get-BearerToken function to get a token for authorisation. Finally we use the OData.Feed (with the bearer token) to authenticate and pull in our results.

I tend to find, whilst OData.Feeds are OK, I have found them to be a bit hit and miss. Most of the time, 95% I would say, you’ll be fine. However I have noticed that the OData.Feed doesn’t always support everything you could need, sometimes you get errors on columns straight away. Sometimes you get columns that you need to expand within PowerBI, you get excited about what you might get and then everything comes back as an error. But don’t let that put you off, this is a great way to handle pagination.

Pre-Requisites
Power Query
let

// Application Registration Permissions needed to make this call : DeviceManagementServiceConfig.Read.All
//                                                               : DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/windowsAutopilotDeviceIdentities/",
    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
            ]
    )

// Formatting

in
    OData

Power Queries available from my Github

Create the query

In order to create the query, once you have all the pre-requisites done select Transform Data (1) from the ribbon, then right-click to create a new blank query (2 & 3). Access the Advanced Editor (4) and insert the Power Query code (5). Once saved, you will observe that this action not only generates the query and all of its associated steps but it should displays the dataset from the Microsoft Graph call.

Explanation

  • When using Logic Apps, data is processed within the logic app and fed back to PowerBi using Web.Contents Power Query. We can do this because the Logic app can return a JSON format we require, and it is using URLs (or rather, web contents). In our use case we pass parameters to the call to facilitate one method or the other. Don’t forget you can hard-configure these variables in the Logic app instead of passing them up from PowerBI
  • There is a cost involved in using and calling logic apps, please investigate this and see if you are comfortable. OData.Feeds are free to use.
  • From PowerBI’s perspective, its undertaking a weblink call and getting back results. Precisely what it would do if you pointed the console to a cloud hosted CSV, for example, or any other web URL. This means that from PowerBI’s perspective the call is direct to a web link and does not form part of a hand-authored query.
  • Finally, data sanitisation may be required after you get the output. This can take the form of changing the title of columns, changing the type of data or replacing values in the columns. The more work you put into data sanitisation, the less stress you’ll put on the report as the data is santitised before the visuals are created.

Relationships

The following relationship should be added here:

Name Setting
Table 1 deviceManagement/managedDevices
Table 1 Column id
Table 2 deviceManagement/windowsAutopilotDeviceIdentities/
Table 2 Column managedDeviceId
Active Relationship Yes
Cross-Filter Direction Both
Relationship Type 1:1

Tip

Whilst you’re creating your tables, you may find that PowerBi tries to automatically create table relationships. Sometimes, it gets this right, other times not so much. Its important to check on these relationships so your tables relate to each other as you expect. The Cross-Filter Direction is important here for when you create visuals on your dashboard because this is going to determine how the visuals filter and the overall look and feel for your end users.

Next post

In the next post I’m going to look at Connectors and Tokens. This one will be a slightly different layout as we can get away without using pagination for these so we’ll use direct queries instead. Therefore there will only be direct queries for you to copy and use and not three tabs for approaches as we’ve been used to over the last couple of posts.

I appreciate you taking the time to read my blog.
Your interest and support keep me motivated to create more content.
If you think others might benefit from this content, please consider sharing it
 ... Jonathan
Location Link
Microsoft Learn Create and Manage Relationships in PowerBi
Share this post