Run CMPivot Queries from Intune Admin Center (Tenant Attach)

In this post, you’ll learn how to run CMPivot queries from Intune admin center. Microsoft Intune makes it easy to run CMPivot queries on tenant attached devices and assess the state of devices in your environment.

To run the CMPivot query on a device in Intune, you must first enable the tenant attach. Tenant attach connects your Intune tenant to your on-premises SCCM environment. Once you have configured the tenant attach, you view and manage devices directly from the Intune Admin Center. Take a look at the guide on enabling SCCM tenant attach.

With Configuration Manager, CMPivot query can query the data in real time on all currently connected devices in the selected collection. Microsoft has extended the CMPivot feature to Intune, where you can run the CMPivot queries on the tenant attached devices. For example, your Helpdesk team can launch real-time cloud queries against a single ConfigMgr managed device and return the results to the Intune admin center.

In this post, we’ll go over the prerequisites and permissions needed to run CMPivot queries from the Intune portal. If the prerequisites and account permissions are not in place, you will receive error 401 or 403 when running CMPivot queries from the Intune portal.

Prerequisites

To run the CMPivot query from the Intune Portal on tenant attached devices, you will need the following prerequisites:

All the prerequisites for Tenant attach: ConfigMgr client details
Configuration Manager 2006 or higher version.
Configuration Manager clients must be upgraded to the latest version.
Target clients require a minimum of PowerShell version 5.
Microsoft Edge, version 77 and later. Google Chrome (latest version).
The Intune tenant attached devices must be online in order to run the CMPivot queries.

Permissions

The user account needs the following permissions to successfully run CMPivot query from Intune Portal:

The Read permission for the device’s Collection in Configuration Manager.
The Run CMPivot permission on the Collection in Configuration Manager.
An Intune role assigned to the user.
The user account needs to be a synced user object in Azure AD (hybrid identity). This means that the user is synced to Azure Active Directory from Active Directory.

Launch CMPivot

Use the following steps to launch the CMPivot:

Sign in to Microsoft Intune Admin Center.
Go to Devices, then select All Devices.
Select a device that is synced from Configuration Manager via tenant attach.
In the left pane, choose CMPivot to launch it.

The CMPivot launches now displaying all the built-in queries. On the right, there is a section dedicated to running the CMPivot query.

Run CMPivot Queries from Intune Portal

In the above step, we saw how to launch the CMPivot from Intune portal. Now we will look at the steps to run CMPivot queries from Intune portal.

From the CMPivot query window, you can run either a built-in query or a custom query. If you’re using a custom query, make sure the query is working. You can use any of the CMPivot queries published on this blog.

We will run a sample query that will list all the local administrators present on the device. To run the CMPivot query from the Intune admin center, copy the below query and paste it to the query input box and select Run.

Administrators | where Name !contains 'Administrator' and Name !contains 'Domain Admins'

The CMPivot query will now list all the local administrators on the co-managed device.

Run CMPivot Queries from Intune Admin Center

In the next example, we’ll run the following CMPivot query to list the recently used applications on the tenant attached device.

CCMRecentlyUsedApplications | where (LastUsedTime > ago(2h)) | project CompanyName, ProductName, ProductVersion, LastUsedTime

If your CMPivot query output is empty, you must start troubleshooting the issue by reviewing the logs. CMPivot queries initiated from the Intune portal are logged in CMGatewayNotificationWorker.log & AdminService.log. You can find these logs on the Configuration Manager site server.

Export CMPivot Query Results in Intune

CMPivot query results can be exported to a.csv file in Intune with a single click. When you run CMPivot queries in Intune, you may receive lengthy output that is difficult to analyze. The best thing you can do is export the query output to a .csv file and analyze the data later using the Excel application.

Here’s how you can export the CMPivot query results to a .csv file in Intune:

After the CMPivot query has been executed, click on the Export option.
On the Export CMPivot query results box, select Download button.
The CMPivot query results are now exported to .csv file and this file is downloaded to your computer.

Save CMPivot queries to favorites

In Microsoft Intune, to keep all of your frequently used queries in one place, you can save your CMPivot queries to favorites folder. You can also add tags to your queries to aid in search and discovery.

The functionality is similar to that which is already available in the Configuration Manager console. The queries saved in the Configuration Manager console will not be added to your Favorites folder automatically. You must create new queries and manually save them in this folder.

To save your query, select the Save option after typing in your query. You can customize the name and tags for your query and click Save.

You can view all your saved favorite queries, under the “Favorites” folder on the left panel, along with all other CMPivot entities.

Close the CMPivot

To close CMPivot and return to the device information, use the X icon in the top right of CMPivot.

Conclusion

CMPivot is a useful tool in to get the data in real time on all currently connected devices in both Intune and Configuration Manager. With Microsoft extending the CMPivot to Intune, IT Admins and other designated personas can quickly assess the state of devices in their environment and take action. I hope this guide helps to get started with CMPivot queries in Intune. In case you have any questions, please leave them in the comments section.