Intune and PowerBI Deep Dive - Part 7 - Managed Applications

post-thumb

In this post we’ll look at Applications within Intune. The Graph response for this will have some additions for applications at the bottom for cross referencing purposes so check those out too. Especially if you use PatchMyPC.

Lets get to it!

Table Of Contents

Graph URL

The graph URL we are using in this section is deviceAppManagement/mobileApps/.
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 : DeviceManagementApps.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceAppManagement/mobileApps/",
    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],
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Formatting
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"@odata.type", "id", "displayName", "description", "publisher", "largeIcon", "createdDateTime", "lastModifiedDateTime", "isFeatured", "privacyInformationUrl", "informationUrl", "owner", "developer", "notes", "uploadState", "publishingState", "isAssigned", "roleScopeTagIds", "dependentAppCount", "supersedingAppCount", "supersededAppCount", "committedContentVersion", "fileName", "size", "installCommandLine", "uninstallCommandLine", "applicableArchitectures", "minimumFreeDiskSpaceInMB", "minimumMemoryInMB", "minimumNumberOfProcessors", "minimumCpuSpeedInMHz", "msiInformation", "setupFilePath", "minimumSupportedWindowsRelease", "displayVersion", "minimumSupportedOperatingSystem", "detectionRules", "requirementRules", "rules", "installExperience", "returnCodes"}, {"@odata.type", "id", "displayName", "description", "publisher", "largeIcon", "createdDateTime", "lastModifiedDateTime", "isFeatured", "privacyInformationUrl", "informationUrl", "owner", "developer", "notes", "uploadState", "publishingState", "isAssigned", "roleScopeTagIds", "dependentAppCount", "supersedingAppCount", "supersededAppCount", "committedContentVersion", "fileName", "size", "installCommandLine", "uninstallCommandLine", "applicableArchitectures", "minimumFreeDiskSpaceInMB", "minimumMemoryInMB", "minimumNumberOfProcessors", "minimumCpuSpeedInMHz", "msiInformation", "setupFilePath", "minimumSupportedWindowsRelease", "displayVersion", "minimumSupportedOperatingSystem", "detectionRules", "requirementRules", "rules", "installExperience", "returnCodes"})

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

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

// Get an Access Token to make Graph Calls (uses Application Registration)
    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],
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Formatting
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"@odata.type", "id", "displayName", "description", "publisher", "largeIcon", "createdDateTime", "lastModifiedDateTime", "isFeatured", "privacyInformationUrl", "informationUrl", "owner", "developer", "notes", "uploadState", "publishingState", "isAssigned", "roleScopeTagIds", "dependentAppCount", "supersedingAppCount", "supersededAppCount", "committedContentVersion", "fileName", "size", "installCommandLine", "uninstallCommandLine", "applicableArchitectures", "minimumFreeDiskSpaceInMB", "minimumMemoryInMB", "minimumNumberOfProcessors", "minimumCpuSpeedInMHz", "msiInformation", "setupFilePath", "minimumSupportedWindowsRelease", "displayVersion", "minimumSupportedOperatingSystem", "detectionRules", "requirementRules", "rules", "installExperience", "returnCodes"}, {"@odata.type", "id", "displayName", "description", "publisher", "largeIcon", "createdDateTime", "lastModifiedDateTime", "isFeatured", "privacyInformationUrl", "informationUrl", "owner", "developer", "notes", "uploadState", "publishingState", "isAssigned", "roleScopeTagIds", "dependentAppCount", "supersedingAppCount", "supersededAppCount", "committedContentVersion", "fileName", "size", "installCommandLine", "uninstallCommandLine", "applicableArchitectures", "minimumFreeDiskSpaceInMB", "minimumMemoryInMB", "minimumNumberOfProcessors", "minimumCpuSpeedInMHz", "msiInformation", "setupFilePath", "minimumSupportedWindowsRelease", "displayVersion", "minimumSupportedOperatingSystem", "detectionRules", "requirementRules", "rules", "installExperience", "returnCodes"})
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 : DeviceManagementApps.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceAppManagement/mobileApps/",
    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
    OData = OData.Feed (
        GraphURL,
            [ Authorization = Bearer ],
            [
                ExcludedFromCacheKey = {"Authorization"},
                ODataVersion = 4,
                Implementation = "2.0"
            ]
    )
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

  • At present no table relationships to add here.

Supported Product Lists

Please ping me a message if you want any others adding here.

Title Notes
PatchMyPC Supported Products Link (CSV)
iVanti Patch Supported Product List (JSON POST)
Microsoft Enterprise App Management (List)

PatchMyPC provide a nice web hosted CSV you can use, you can use the URL and pass that straight to PowerBI and pull the list of products in directly (love this!). iVanti patch use a web site to POST a JSON to their back end which will produce a download for a CSV, however, its a bit strange in how its hosted so no direct download link and there are three different OS platforms so you’ll need three different CSVs and then to Merge them (not great, but workable). Microsoft Advanced App Management (through Intune Suite) shows their apps on the website as an unordered list (why Microsoft!? Just use a table, please!) so not easy to grab from the website and pull into PowerBI.

Additional

PatchMyPC customers can benefit from the fact that they classify their apps quite nicely. When you pull in your apps data, you may notice that PatchMyPC add notes to the app. An application created by PatchMyPC has PMPCApp: in the notes followed, by a GUID and an update (which is essentially an app anyway but with slightly different properties) has PMPCUpdate: in the notes followed by a GUID. Like so;

PatchMyPC Notes

You may also notice that anything generated by PatchMyPC has a unique filename. It will have PatchMyPC-ScriptRunner.intunewin in it something which you might be able to leverage to create a nice PowerBI dashboard based solely upon PatchMyPC.

Next post

Up next Autopilot Devices . Reminder, the next few posts will feel very alike with odd changes. I am hoping chunks become familiar to you and the queries on my GitHub will act as a ‘one-stop shop’ to go get queries at present we’re focussing on getting some data into PowerBI rather than creating a dashboard, that will come in time as we build this up together.

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