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:

Enabling the ConfigMgr administration service through the cloud management gateway

This week is all about the administration service in Configuration Manager. More specifically, about enabling the Configuration Manager administration service via the cloud management gateway (CMG) to make it available over the Internet. The administration service provides API interoperability access to WMI over HTTPS via the SMS Provider. This REST API can be used in place of a custom web service to access information of the Configuration Manager site. Some really good information and starting points about this subject can be found at this blog post by Adam Gross. In this post I’ll skip the basics and specifically look at making the administration service available over the Internet. I want to provide in my own style what the configuration requirements are and why they are needed. I’ll start this post by showing the required configurations in Configuration Manager and in Azure AD and I’ll end this post by retrieving the most common parameters for scripting.

Before starting with the actual configurations, I want to post a little thank you message: Thank you Sandy for answering my (dumb) questions while I should simply read better.

Configuring the SMS Provider properties

The administration service is available with the installation of the SMS Provider. Every site system with an SMS Provider has the administration service. Before being able to enable the SMS Provider over the CMG, the following prerequisites should be in-place:

  • The server that hosts the SMS Provider role requires .NET 4.5.2 or later
  • Enable the SMS Provider to use a certificate, by either using Enhanced HTTP or by manually binding a PKI-based certificate on the server that hosts the SMS Provider role
  • A running CMG (as I’m not going through that installation)

When those prerequisites are in-place, the SMS Provider can be configured to allow CMG traffic for the administration service by following the next three steps.

  1. Open the Configuration Manager administration console and navigate to Administration > Overview > Site Configuration > Servers and Site System Roles
  2. Select the server that hosts the SMS Provider role, select the SMS Provider role and click Properties in the Site Role tab to open the Provider role properties dialog box
  3. On the Provider role properties dialog box, select Allow Configuration Manage cloud management gateway traffic for administration service and click OK

Register a new app with Azure AD

For accessing the administration service via the CMG, two apps must be created within Azure AD, 1) a Web app (also known as a Server app within Configuration Manager) that is used for making the administration service available and 2) a Native app (also known as a Client app within Configuration Manager) that is used for obtaining an access token for the user. That access token can be sent in a request to the Web app, which authorises the user and returns the administration service.

During the creation of the cloud services within Configuration Manager a Web app and a Native are already created. I need to (and can) access the administration service via that created Web app, but I don’t want to reuse the existing Native app as I need to make some adjustments and I don’t want to interfere with existing functionalities. The following steps walk through the registration and configuration of a new Native app with the required configurations to obtain and access token for the user and be able to sent that token in a request to the Web app.

  1. Open the Azure portal and navigate to Azure Active Directory  > App registrations to open the App registrations blade
  2. On the App registrations blade, click New registration to open the Register an application blade
  3. On the Register an application blade, provide the following information (as also shown below) and click Register
  • Name: Provide a valid name for the Web app (in this post: ConfigMgrAdminService)
  • Supported account types: Select Accounts in this organisational directory only ({yourTenant} only – Single tenant)
  • Redirect URI (optional): Select Public client/native (mobile & desktop) and provide https://login.microsoftonline.com/common/oauth2/nativeclient as Redirect URI

Note: The mentioned redirect URI, is the latest recommended value for desktop applications running on Windows (see also: https://docs.microsoft.com/en-us/azure/active-directory/develop/scenario-desktop-app-registration).

  1. After the registration of the app, navigate to Authentication to open the Authentication blade
  2. On the Authentication blade, navigate to the Default client type section and select Yes with Required for the use of the following flows where a redirect URI is not used (as shown below) and click Save
  1. Navigate to API permissions to open the API permissions blade
  2. On the API permissions blade, click Add a permission to open the Request API permissions blade
  3. On the Request API permissions blade, select APIs my organisation uses and select the Web app – the standard name of that app is ConfigMgrService (as shown below) – that was initially created during the setup of the cloud services to open the specific API permissions blade
  1. On the specific API permissions blade, select Delegated permissions, select user_impersonation and click Add permissions (as shown below) to return to the API permissions blade
  1. On the API permissions blade, select Grant admin consent for {yourTenant} (as shown below

Retrieve the parameters to start with PowerShell

After configuring the SMS Provider properties, registering and configuring the Native app, the administration service is available via the CMG. The next step is to actually externally connect with the administration service. However, this might be an open door, but before doing that it’s good to understand that the user that is authentication and connecting with the administration service must have sufficient permissions within Configuration Manager.

At this moment I won’t provide an example, that might be something for a future post, but for now I’ll refer to this great post by Zeng Yinghua (also known as Sandy) and this repository about the Microsoft Graph (as the idea for retrieving a token is the same). The main challenge in any of those scripts is getting the token. To successfully achieve that, the following information is often required.

  1. Application (client) ID of the Native app that is named ConfigMgrAdminService in this post. That information can be found in the Azure portal at Azure Active Directory  > App registrations > ConfigMgrAdminService > Overview (shown in the figure below with number 1).
  2. Tenant ID of the Native app that is named ConfigMgrAdminService in this post. That information can be found in the Azure portal at Azure Active Directory  > App registrations > ConfigMgrAdminService > Overview (shown in the figure below with number 2).
  3. Redirect URI of the Native app that is named ConfigMgrAdminService in this post. That information can be found in the Azure portal at Azure Active Directory  > App registrations > ConfigMgrAdminService > Overview (shown in the figure below with number 3) or copying the information that was provided in step 3 during the registration of Native app in Azure AD.
  1. Application ID URI of the Web app that is named by default ConfigMgrService. That information can be found in the Azure portal at Azure Active Directory  > App registrations > ConfigMgrService > Overview (shown in the figure below with number 4)
  1. External URL of the administration service. That information can be the easiest retrieved in SQL by using the query below on the ConfigMgr database
select ExternalEndpointName, ExternalUrl from vProxy_Routings
where ExternalEndpointName = 'AdminService'

More information

For more information about Configuration Manager administration service, please refer to the documentation about the SMS Provider.