Nosari20
Get Intune data with PowerBI

Get Intune data with PowerBI

Published on: - Last update:
Url: https://nosari20.github.io/posts/intune-powerbi/

Content

Use Intune data whareouse

  1. In Microsoft Endpoint Manager portal go to Reports > Intune data warehouse > Data warehouse and copy the OData feed URL

Intune OData feed URL

  1. In PowerBi create a new OData data source

Create a new OData data source

  1. Paste OData feed URL

Paste OData feed URL

  1. Choose Organizational account as authentication method

Choos authentication method

  1. Select desired tables then click Load

Select tables

Use Graph API (advanced)

Why use Graph API?

Intune data warehouse has a limited set of available data tables. For example, you cannot filter devices by group membership. Using Graph API can resolve this limitation but we cannot browse the whole database with Graph API so it will be difficult to create relationships between results.

Prerequisites

Custom query using PowerQuery M language

  1. Create a blank query

Create blank query

  1. Open advanced editor for the created query

Open advanced editor

  1. Use the following code
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

let
    // Step 1: Get OAuth token
    token_uri = "https://login.windows.net/" & #"Directory ID" & "/oauth2/token",
    resource="https://graph.microsoft.com",
    tokenResponse = Json.Document(Web.Contents(token_uri,
    [
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = #"Client ID", //managed parameter
                resource = resource,
                grant_type = "client_credentials", //managed parameter
                client_secret = #"Client Secret" //managed parameter
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
    bearer = "Bearer " & access_token,


    // Step 2: Perform http request and covert the result to JSON
    GetJson = Json.Document(Web.Contents("https://graph.microsoft.com/beta/users?", [Headers=[Accept="application/json", Authorization=bearer]])),
        
    // Step 3: Convert JSON to table ()
    Result = Table.FromRecords(GetJson[value])
in
    Result
  1. Create managed parameters for Directory ID, Client ID and Client Secret

Create new parameter - step 1

Create new parameter - step 2

  1. Edit permissions and privacy for each data source

Edit permission and privacy

  1. Click on Refresh Preview et voilà!

Result

Next steps

Sources / usefull resources