Intune and PowerBI Deep Dive - Part 6 - Managed Devices

post-thumb

In this post we are finally going to look at getting some data into PowerBI to play around with. We are going to use a query in PowerBI to go and retrieve our managed devices information. Remember discussing pagination for APIs? Should you have more than 1000 devices we’re also going to need to consider Pagination but don’t worry there are three methods to do this discussed below.

Now, when you first approach this task you might consider employing a coding mindset akin to PowerShell, such as fetching the initial 1000 devices and implementing a do-while loop to retrieve the remaining ones using a method such as checking for the presence of another @Data.nextlink and, if available, using it to fetch additional devices until the next link is no longer present. Although these methods work, it poses a challenge in the way PowerBI manages data within PowerBI Desktop. It involves a ‘hand-authored’ query. While effective within PowerBI Desktop for obtaining the required data, it is important to note that this method will not allow the publication of the report to the PowerBI service. This is why I’ve looked into other methods to do the ‘heavy lifting’. A hand-authored query will prevent you publishing your report and you will see this if you look at the data source settings (as shown in part 1 )

Alt Text

We’ve already discussed why we don’t want to do that, how we should use one of a number of ways to grab our data and even how to discover what we need beforehand. So lets make a start getting some data into PowerBI using those methods. This first Graph URL is going to get us our managed devices from Intune and use a list call to pull in hardware information.

Table Of Contents

Graph URL

The graph URL we are using in this section is deviceManagement/managedDevices/.
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 : DeviceManagementManagedDevices.Read.All

// 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 & """
            }",
    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", "userId", "deviceName", "ownerType", "managedDeviceOwnerType", "managementState", "enrolledDateTime", "lastSyncDateTime", "chassisType", "operatingSystem", "deviceType", "complianceState", "jailBroken", "managementAgent", "osVersion", "easActivated", "easDeviceId", "easActivationDateTime", "aadRegistered", "azureADRegistered", "deviceEnrollmentType", "lostModeState", "activationLockBypassCode", "emailAddress", "azureActiveDirectoryDeviceId", "azureADDeviceId", "deviceRegistrationState", "deviceCategoryDisplayName", "isSupervised", "exchangeLastSuccessfulSyncDateTime", "exchangeAccessState", "exchangeAccessStateReason", "remoteAssistanceSessionUrl", "remoteAssistanceSessionErrorDetails", "isEncrypted", "userPrincipalName", "model", "manufacturer", "imei", "complianceGracePeriodExpirationDateTime", "serialNumber", "phoneNumber", "androidSecurityPatchLevel", "userDisplayName", "configurationManagerClientEnabledFeatures", "wiFiMacAddress", "deviceHealthAttestationState", "subscriberCarrier", "meid", "totalStorageSpaceInBytes", "freeStorageSpaceInBytes", "managedDeviceName", "partnerReportedThreatState", "retireAfterDateTime", "preferMdmOverGroupPolicyAppliedDateTime", "autopilotEnrolled", "requireUserEnrollmentApproval", "managementCertificateExpirationDate", "iccid", "udid", "roleScopeTagIds", "windowsActiveMalwareCount", "windowsRemediatedMalwareCount", "notes", "configurationManagerClientHealthState", "configurationManagerClientInformation", "ethernetMacAddress", "physicalMemoryInBytes", "processorArchitecture", "specificationVersion", "joinType", "skuFamily", "skuNumber", "managementFeatures", "enrollmentProfileName", "bootstrapTokenEscrowed", "deviceFirmwareConfigurationInterfaceManaged", "hardwareInformation", "deviceActionResults", "usersLoggedOn", "chromeOSDeviceInfo"}, {"id", "userId", "deviceName", "ownerType", "managedDeviceOwnerType", "managementState", "enrolledDateTime", "lastSyncDateTime", "chassisType", "operatingSystem", "deviceType", "complianceState", "jailBroken", "managementAgent", "osVersion", "easActivated", "easDeviceId", "easActivationDateTime", "aadRegistered", "azureADRegistered", "deviceEnrollmentType", "lostModeState", "activationLockBypassCode", "emailAddress", "azureActiveDirectoryDeviceId", "azureADDeviceId", "deviceRegistrationState", "deviceCategoryDisplayName", "isSupervised", "exchangeLastSuccessfulSyncDateTime", "exchangeAccessState", "exchangeAccessStateReason", "remoteAssistanceSessionUrl", "remoteAssistanceSessionErrorDetails", "isEncrypted", "userPrincipalName", "model", "manufacturer", "imei", "complianceGracePeriodExpirationDateTime", "serialNumber", "phoneNumber", "androidSecurityPatchLevel", "userDisplayName", "configurationManagerClientEnabledFeatures", "wiFiMacAddress", "deviceHealthAttestationState", "subscriberCarrier", "meid", "totalStorageSpaceInBytes", "freeStorageSpaceInBytes", "managedDeviceName", "partnerReportedThreatState", "retireAfterDateTime", "preferMdmOverGroupPolicyAppliedDateTime", "autopilotEnrolled", "requireUserEnrollmentApproval", "managementCertificateExpirationDate", "iccid", "udid", "roleScopeTagIds", "windowsActiveMalwareCount", "windowsRemediatedMalwareCount", "notes", "configurationManagerClientHealthState", "configurationManagerClientInformation", "ethernetMacAddress", "physicalMemoryInBytes", "processorArchitecture", "specificationVersion", "joinType", "skuFamily", "skuNumber", "managementFeatures", "enrollmentProfileName", "bootstrapTokenEscrowed", "deviceFirmwareConfigurationInterfaceManaged", "hardwareInformation", "deviceActionResults", "usersLoggedOn", "chromeOSDeviceInfo"}),
    #"Expanded hardwareInformation" = Table.ExpandRecordColumn(#"Expanded Column1", "hardwareInformation", {"serialNumber", "totalStorageSpace", "freeStorageSpace", "imei", "meid", "manufacturer", "model", "phoneNumber", "subscriberCarrier", "cellularTechnology", "wifiMac", "operatingSystemLanguage", "isSupervised", "isEncrypted", "batterySerialNumber", "batteryHealthPercentage", "batteryChargeCycles", "isSharedDevice", "tpmSpecificationVersion", "operatingSystemEdition", "deviceFullQualifiedDomainName", "deviceGuardVirtualizationBasedSecurityHardwareRequirementState", "deviceGuardVirtualizationBasedSecurityState", "deviceGuardLocalSystemAuthorityCredentialGuardState", "osBuildNumber", "operatingSystemProductType", "ipAddressV4", "subnetAddress", "esimIdentifier", "systemManagementBIOSVersion", "tpmManufacturer", "tpmVersion", "sharedDeviceCachedUsers"}, {"serialNumber.1", "totalStorageSpace", "freeStorageSpace", "imei.1", "meid.1", "manufacturer.1", "model.1", "phoneNumber.1", "subscriberCarrier.1", "cellularTechnology", "wifiMac", "operatingSystemLanguage", "isSupervised.1", "isEncrypted.1", "batterySerialNumber", "batteryHealthPercentage", "batteryChargeCycles", "isSharedDevice", "tpmSpecificationVersion", "operatingSystemEdition", "deviceFullQualifiedDomainName", "deviceGuardVirtualizationBasedSecurityHardwareRequirementState", "deviceGuardVirtualizationBasedSecurityState", "deviceGuardLocalSystemAuthorityCredentialGuardState", "osBuildNumber", "operatingSystemProductType", "ipAddressV4", "subnetAddress", "esimIdentifier", "systemManagementBIOSVersion", "tpmManufacturer", "tpmVersion", "sharedDeviceCachedUsers"})
in
    #"Expanded hardwareInformation"

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 : DeviceManagementManagedDevices.Read.All

// 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 & """
            }",
    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", "userId", "deviceName", "ownerType", "managedDeviceOwnerType", "managementState", "enrolledDateTime", "lastSyncDateTime", "chassisType", "operatingSystem", "deviceType", "complianceState", "jailBroken", "managementAgent", "osVersion", "easActivated", "easDeviceId", "easActivationDateTime", "aadRegistered", "azureADRegistered", "deviceEnrollmentType", "lostModeState", "activationLockBypassCode", "emailAddress", "azureActiveDirectoryDeviceId", "azureADDeviceId", "deviceRegistrationState", "deviceCategoryDisplayName", "isSupervised", "exchangeLastSuccessfulSyncDateTime", "exchangeAccessState", "exchangeAccessStateReason", "remoteAssistanceSessionUrl", "remoteAssistanceSessionErrorDetails", "isEncrypted", "userPrincipalName", "model", "manufacturer", "imei", "complianceGracePeriodExpirationDateTime", "serialNumber", "phoneNumber", "androidSecurityPatchLevel", "userDisplayName", "configurationManagerClientEnabledFeatures", "wiFiMacAddress", "deviceHealthAttestationState", "subscriberCarrier", "meid", "totalStorageSpaceInBytes", "freeStorageSpaceInBytes", "managedDeviceName", "partnerReportedThreatState", "retireAfterDateTime", "preferMdmOverGroupPolicyAppliedDateTime", "autopilotEnrolled", "requireUserEnrollmentApproval", "managementCertificateExpirationDate", "iccid", "udid", "roleScopeTagIds", "windowsActiveMalwareCount", "windowsRemediatedMalwareCount", "notes", "configurationManagerClientHealthState", "configurationManagerClientInformation", "ethernetMacAddress", "physicalMemoryInBytes", "processorArchitecture", "specificationVersion", "joinType", "skuFamily", "skuNumber", "managementFeatures", "enrollmentProfileName", "bootstrapTokenEscrowed", "deviceFirmwareConfigurationInterfaceManaged", "hardwareInformation", "deviceActionResults", "usersLoggedOn", "chromeOSDeviceInfo"}, {"id", "userId", "deviceName", "ownerType", "managedDeviceOwnerType", "managementState", "enrolledDateTime", "lastSyncDateTime", "chassisType", "operatingSystem", "deviceType", "complianceState", "jailBroken", "managementAgent", "osVersion", "easActivated", "easDeviceId", "easActivationDateTime", "aadRegistered", "azureADRegistered", "deviceEnrollmentType", "lostModeState", "activationLockBypassCode", "emailAddress", "azureActiveDirectoryDeviceId", "azureADDeviceId", "deviceRegistrationState", "deviceCategoryDisplayName", "isSupervised", "exchangeLastSuccessfulSyncDateTime", "exchangeAccessState", "exchangeAccessStateReason", "remoteAssistanceSessionUrl", "remoteAssistanceSessionErrorDetails", "isEncrypted", "userPrincipalName", "model", "manufacturer", "imei", "complianceGracePeriodExpirationDateTime", "serialNumber", "phoneNumber", "androidSecurityPatchLevel", "userDisplayName", "configurationManagerClientEnabledFeatures", "wiFiMacAddress", "deviceHealthAttestationState", "subscriberCarrier", "meid", "totalStorageSpaceInBytes", "freeStorageSpaceInBytes", "managedDeviceName", "partnerReportedThreatState", "retireAfterDateTime", "preferMdmOverGroupPolicyAppliedDateTime", "autopilotEnrolled", "requireUserEnrollmentApproval", "managementCertificateExpirationDate", "iccid", "udid", "roleScopeTagIds", "windowsActiveMalwareCount", "windowsRemediatedMalwareCount", "notes", "configurationManagerClientHealthState", "configurationManagerClientInformation", "ethernetMacAddress", "physicalMemoryInBytes", "processorArchitecture", "specificationVersion", "joinType", "skuFamily", "skuNumber", "managementFeatures", "enrollmentProfileName", "bootstrapTokenEscrowed", "deviceFirmwareConfigurationInterfaceManaged", "hardwareInformation", "deviceActionResults", "usersLoggedOn", "chromeOSDeviceInfo"}),
    #"Expanded hardwareInformation" = Table.ExpandRecordColumn(#"Expanded Column1", "hardwareInformation", {"serialNumber", "totalStorageSpace", "freeStorageSpace", "imei", "meid", "manufacturer", "model", "phoneNumber", "subscriberCarrier", "cellularTechnology", "wifiMac", "operatingSystemLanguage", "isSupervised", "isEncrypted", "batterySerialNumber", "batteryHealthPercentage", "batteryChargeCycles", "isSharedDevice", "tpmSpecificationVersion", "operatingSystemEdition", "deviceFullQualifiedDomainName", "deviceGuardVirtualizationBasedSecurityHardwareRequirementState", "deviceGuardVirtualizationBasedSecurityState", "deviceGuardLocalSystemAuthorityCredentialGuardState", "osBuildNumber", "operatingSystemProductType", "ipAddressV4", "subnetAddress", "esimIdentifier", "systemManagementBIOSVersion", "tpmManufacturer", "tpmVersion", "sharedDeviceCachedUsers"}, {"serialNumber.1", "totalStorageSpace", "freeStorageSpace", "imei.1", "meid.1", "manufacturer.1", "model.1", "phoneNumber.1", "subscriberCarrier.1", "cellularTechnology", "wifiMac", "operatingSystemLanguage", "isSupervised.1", "isEncrypted.1", "batterySerialNumber", "batteryHealthPercentage", "batteryChargeCycles", "isSharedDevice", "tpmSpecificationVersion", "operatingSystemEdition", "deviceFullQualifiedDomainName", "deviceGuardVirtualizationBasedSecurityHardwareRequirementState", "deviceGuardVirtualizationBasedSecurityState", "deviceGuardLocalSystemAuthorityCredentialGuardState", "osBuildNumber", "operatingSystemProductType", "ipAddressV4", "subnetAddress", "esimIdentifier", "systemManagementBIOSVersion", "tpmManufacturer", "tpmVersion", "sharedDeviceCachedUsers"})
in
    #"Expanded hardwareInformation"
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    : DeviceManagementManagedDevices.Read.All

// 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
            ]
    )
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

  • I will return here as I add more posts and update the table relationships, as this is our first one, nothing to add here just now but in order to form good reports, spend most of your time sanitising your data and forming good table relationships. I know the fun part is making a report, but the effort put in here is very much worth it in the long run.

Next post

Up next Applications .

I appreciate you taking the time to read my blog.
Please give it a share for me.
Jonathan
Share this post