Easily exporting Intune reports using Microsoft Graph

This week a short blog post about Intune reports and more specifically about exporting Intune reports by using Microsoft Graph. Since recently, all reports that are available in the (new) Intune reporting infrastructure are available for export. That export can be achieved from a single top-level export API. Simply use Microsoft Graph API to make the required HTTP call(s). The result of the HTTP call(s) will be a downloadable ZIP-file that contains a CSV-file. That CSV-file contains an export of the latest real-time information and can be imported in EXCEL for some simple data analyses, or in Power BI for more advanced data analyses and visualizations. In this post I’ll show how to use Microsoft Graph to export Intune reports and I’ll show the results of the export.

Export Intune reports

Let’s start with the easiest part, which is knowing the correct Microsoft Graph API endpoint. That’s the endpoint below.

https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs

The more challenging part is the parameters that can be submitted in the request body. Basically there are the following three main parameters that can be submitted in the request body to define the export request:

  • reportName: This is a required parameter (type String) that contains the name of the report that should be exported.
  • filter: This is an optional parameter, for most reports, (type String) that can be used to filter the dataset.
  • select: This is an optional parameter (type String collection) that can be used to select specific columns of the dataset. When nothing is specified, a default set of columns, which for most reports is the entire dataset, is selected.

Note: The documentation about exporting reports provides information about the available reports, the name of the reports, the information that can be used to filter the data and the properties that can be used to select specific columns of the data.

Now when making the request, the reportName parameter must be provided as part of the request body. That parameter contains the name of the report that should be exported. Below is an example of an export request for the Devices report that filters on specific data and only selects specific columns. That example can be used in a HTTP POST method on the request. The HTTP POST method is used to perform an action to export the report. A simple method to perform this action is by using Microsoft Graph Explorer. In Graph Explorer, simply select POST, provide the mentioned endpoint as URL, provide the example below as request body and click Run query.

{
    "reportName": "Devices",
    "filter": "(ManagementAgents eq '2') and (OwnerType eq '1')",
    "select": [
        "DeviceName",
	"DeviceType",
	"Ownership",
        "ManagedBy",
        "managementState",
        "complianceState",
        "OS",
        "OSVersion",
        "LastContact",
        "UPN"
    ],
    "localization": "true",
    "ColumnName": "ui"
}

Note: In most cases I would suggest to not filter the data. I only used a filter to show how it works. A tool like Power BI can be used to actually filter the data and to create some nice data analyses and visualizations. Not filtering the data, when exporting the data, leaves more room for a good interpretation of the data.

After posting the provided HTTP POST method on the request, Microsoft Graph returns a response message. That response message contains the information that was provided in the request and the id and status of the request. Especially the id is important, as that id should be used to follow the status of the request.

{
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#deviceManagement/reports/exportJobs/$entity",
    "id": "Devices_115d909a-7574-4e94-b236-e6e082ab6788",
    "reportName": "Devices",
    "filter": "(ManagementAgents eq '2') and (OwnerType eq '1')",
    "select": [
        "DeviceName",
        "DeviceType",
        "Ownership",
        "ManagedBy",
        "managementState",
        "complianceState",
        "OS",
        "OSVersion",
        "LastContact",
        "UPN"
    ],
    "format": "csv",
    "snapshotId": null,
    "status": "notStarted",
    "url": null,
    "requestDateTime": "2020-10-07T09:42:06.388268Z",
    "expirationDateTime": "0001-01-01T00:00:00Z"
}

To follow the status of the export request, the id can be used to query for an updated status. In Graph Explorer, simply select Get, provide something similar to the example below (just adjust the provided id) as the URL and click Run query.

https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('Devices_115d909a-7574-4e94-b236-e6e082ab6788')

Once the status of the export request changes to completed, the url in the response will contain a link to a downloadable ZIP-file in a storage blob.

{
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#deviceManagement/reports/exportJobs/$entity",
    "id": "Devices_115d909a-7574-4e94-b236-e6e082ab6788",
    "reportName": "Devices",
    "filter": "(ManagementAgents eq '2') and (OwnerType eq '1')",
    "select": [
        "DeviceName",
        "DeviceType",
        "Ownership",
        "ManagedBy",
        "managementState",
        "complianceState",
        "OS",
        "OSVersion",
        "LastContact",
        "UPN"
    ],
    "format": "csv",
    "snapshotId": null,
    "status": "completed",
    "url": "https://amsub0201repexpstorage.blob.core.windows.net/a3283525-8b8f-428c-a3f6-774ec1f94b6d/Devices_115d909a-7574-4e94-b236-e6e082ab6788.zip?sv=2019-02-02&sr=b&sig=Rm301BTLjYTEmTNl7WHk1UL2bu6TKYIhezlpH8lzveU%3D&skoid=1db6df02-4c8b-4cb3-8394-7ac2390642f8&sktid=72f988bf-86f1-41af-91ab-2d7cd011db47&skt=2020-10-07T09%3A43%3A10Z&ske=2020-10-07T15%3A42%3A52Z&sks=b&skv=2019-02-02&se=2020-10-07T15%3A42%3A52Z&sp=r",
    "requestDateTime": "2020-10-07T09:42:06.388268Z",
    "expirationDateTime": "2020-10-07T15:42:52.0376315Z"
}

Intune reports result

The downloaded ZIP-file has the name of the id of the generated report. That ZIP-file contains a CSV-file and that also has the name of the id of the generated report. After exporting the CSV-file, the data can be imported in anyone’s favorite tool. That can be as simple as EXCEL, or a bit more advanced as Power BI. Especially the latter provides some real (simple) capabilities to transform this data into a report. Below are some examples. Figure 1 provides a quick overview of the exported data in a table format.

Figure 2 also provides a quick overview of a similar exported dataset in a table format, but in this case without the filtering of the data. The main goal is to show my earlier note, which will be even clearer with the next figure.

Figure 3 provides an overview of a visualization of both datasets. The main goal is to show that the non-filtered dataset provides a lot more flexibility when analyzing the data. The totally green pie charts are the filtered dataset and the colored pie charts are the non-filtered datasets.

More information

For more information about exporting Intune reports by using Graph APIs, refer to the following docs:

Super easy start with reporting and the Intune Data Warehouse

This week is all about creating awareness for the reporting capabilities of Microsoft Intune. An often heard request. An introduction to the recently introduced Intune Data Warehouse and how easy it can be used to build reports that provide insight into the Intune environment. The Intune Data Warehouse provides access to more information about the Intune environment than the Azure portal. With the Intune Data Warehouse it’s possible to access historical Intune data, data refreshed on a daily cadence and a data model using the OData standard. In this blog post I’ll show how to easily connect to the Intune Data Warehouse, using two different methods, and I’ll end this post with the end result after connecting.

Requirements

Before starting with creating the connection to the Intune Data Warehouse, it’s important to be aware of the authentication and authorization requirements. This is all based on Azure AD credentials and Intune role-based access control (RBAC). By default, all global administrators and Intune service administrators, of the tenant, have access to the Intune Data warehouse. When adding custom Intune roles, the user can get access by providing the Intune Data Warehouse > Read permission to the role.

Besides the mentioned permissions, a Microsoft Intune license must be assigned to each user that is accessing the Intune Data Warehouse.

Connection

Let’s start with making a connection with the Intune Data Warehouse. This can be done by simply using an OData URL to connect to the RESTful endpoint in the Intune Data Warehouse API or by using a Power BI file that contains the connection information. With both methods I’ll use Power BI Desktop, which is available for download here: https://powerbi.microsoft.com/en-us/desktop/

Method 1: Load data using OData URL

The first method is loading data by using the OData URL. With a user authenticated to Azure AD, the OData URL connects to the RESTful endpoint in the Intune Data Warehouse API that exposes the data model to, in my case, Power BI Desktop. The following 7 steps walk through creating the connection and loading the selected information from the Intune Data Warehouse.

1 Open the Azure portal and navigate to Intune;
2 On the dashboard click Set up Intune Data Warehouse below Other tasks to open the Intune Data Warehouse blade;
3 IDW_Setup_Method1On the Intune Data Warehouse blade, select Copy behind the custom feed URL;
4 Now open Power BI Desktop;
5 On the Home tab, select Get Data > OData feed;
6 PB_ODataSelect Basic, paste the custom feed URL and click OK;
7 PB_NavigatorIn the Navigator select the required tables and click Load;

Note: When signed in with the correct permissions this will start loading the information of the tenant from the Intune Data Warehouse.

Method 2: Load data and reports using Power BI file (pbix)

The second method is loading data and prebuilt reports using a downloaded Power BI file (pbix). That file contains the connection information for the tenant and contains a set of prebuilt reports based on the Intune Data Warehouse data model. The following 6 steps walk through downloading the pbix file and opening the prebuilt reports.

1 Open the Azure portal and navigate to Intune;
2 On the dashboard click Set up Intune Data Warehouse below Other tasks to open the Intune Data Warehouse blade;
3 IDW_Setup_Method2On the Intune Data Warehouse blade, click Download Power BI file and save the pbix file;
4 Now open Power BI Desktop;
5 On the File menu, select Open;
6 PB_OpenIn the Open dialog box, select the pbix file and click Open;

Note: When signed in with the correct permissions this will start loading the information of the tenant from the Intune Data Warehouse and built the reports.

Result

Now let’s end this post by having a quick look at the result of the 2 connection methods.

Method 1: Load data using OData URL

The result of the first method, of loading data by using the OData URL, is fairly basic. Below is an  example of the empty framework that will be available when connecting with the Intune Data warehouse.

PB_ReportStart

This example shows that, with number 1, the reports/dashboards can be created and that, with number 2, the user can switch between the reports, the data/tables and the relationships between the tables. This enables users that are familiar with Power BI Desktop to easily create reports/dashboards.

Method 2: Load data and reports using Power BI file (pbix)

The result of the second method, of loading data and prebuilt reports, is pretty nice. Let’s have a look at an example of the prebuilt reports below. It will show that this is an very easy method to start with reporting and the Intune Data Warehouse.

PB_Reports

This example shows that the pbix file contains connection settings for the tenant, and, with number 1, that it contains the following sample reports and dashboards:

  • Devices
  • Enrollment
  • App protection policy
  • Compliance policy
  • Device configuration profiles
  • Software updates
  • Device inventory logs

With number 2, the user can switch between the prebuilt reports, the data/tables and the relationships between the tables. This is standard information, but very useful for creating custom reports.

Note: Keep in mind that the Intune Data Warehouse data model isn’t complete yet. More information will be made available through this data model. Updates and changes are logged here: https://docs.microsoft.com/en-us/intune/reports-changelog

More information

For more information about reporting and the Intune Data Warehouse, please refer to the following articles: