As Sabaton beautifully puts it in their song “Shiroyama,” when the new meets the old, it always ends the ancient ways.” This blog post isn’t a history lesson, though. Instead, Rudy and Liviu are here to explore modern tools that allow us to query data from clients in real-time: CMPivot vs. Device Query.
Does the legacy approach hold its ground in this strong clash between the old and the new? We’re about to find out.
Before we dive in, here are a few key points: CMPivot is a tool specific to ConfigMgr, while DeviceQuery is exclusive to Intune.
CMPivot
It is a handy real-time querying tool built into Microsoft Endpoint Configuration Manager (ConfigMgr), which you might know as the old System Center Configuration Manager (SCCM). With this tool, IT admins can pull live data directly from clients managed by ConfigMgr, making it great for troubleshooting and getting immediate insights. Whether you need to check installed applications, running processes, device properties, or other details, CMPivot lets you gather this info quickly without waiting for the hardware inventory cycle to run.
A CMPivot query can also run when executed from the Intune Admin Portal if the ConfigMgr device is uploaded through CloudAttach or if the device is co-managed.
To ensure a fair comparison, we will the queries directly from Intune on a co-managed device.
Prerequisites and Permissions
Because we are running the queries from Intune, there are a few key things to keep in mind before you can use it:
CloudAttach :To manage devices from the Microsoft Intune Admin Center, you must configure CloudAttach (formerly known as TenantAttach) in ConfigMgr. This configuration uploads all devices to the Intune Admin Center, allowing you to manage them from there.
Note: Ensure that all devices are uploaded to the Admin Center. Even if you choose not to co-manage the devices, you can still run CMPivot from the Intune Admin Center. Uploading the devices is enough
3. User account permissions: To use CMPivot, you need a hybrid user account from the Intune Admin Center.
This account must have the minimum permissions in both ConfigMgr AND Intune to run this type of queries.
Multiple components are involved in authenticating the user. The Admin Service REST API leverages the Service Connection Point, which, in turn, uses the CloudAttach App Registration to sync the data between the on-premises site and Intune.
When you run a query, the SMS_Notification_Server ConfigMgr component processes the request, and it forwards it to the SMS_Notification_Server component. As a next step, a task is being sent from the Management Point to the client over the BGBServer (Fast Channel Notifications) component.
It will fail if you try to run the query with an Azure AD account, even if it has Global Admin permissions. You’ll get the error message shown in the screenshot below:
If you want to grant someone permissions in ConfigMgr only to run queries, then you need to grant them the built-in “CMPivot Administrator” security role at least.
The same user account must have an Intune role assigned.
Global Reader permissions were enough to run a query when writing this article.
Using CMPivot
Once everything is configured, you can run your queries from the Intune Admin Center. Navigate to Intune Admin Center → Devices → Windows. You’ll be able to select any ConfigMgr or co-managed device to run queries and access data in real time.
Sample queries:
CMPivot uses Kusto Query Language (KQL). Here are a few examples:
Installed Apps by Publisher Name
InstalledSoftware | where Publisher == ‘PublisherName’
Installed Apps Not from Microsoft
InstalledSoftware | project Device, ProductName, Publisher, ProductVersion | where Publisher !like ‘Microsoft%’
Devices with Less Than 10% Free Disk Space on C Drive:
Disk
| project Device, Name, Size, FreeSpace
| where (FreeSpace * 100 / Size) < 10
| where Name == ‘C:’
.NET Framework Versions with a summarized value as a result:
Registry(‘HKLM:SOFTWAREMicrosoftNET Framework SetupNDPv4Full’)
| where (Property == ‘Release’)
| summarize count() by Value
Many more examples can be found in this Microsoft Learn document.
Limitations in the Intune
Executing queries in the Intune portal is limited compared to the options in the ConfigMgr Console.
Custom hardware inventory classes you collect data on won’t appear in Intune.
While the data can be exported to a CSV file from both, Intune allows a maximum of 500 entries.
From ConfigMgr you can create a collection directly from CMPivot with the devices resulting from your query. It is not possible to create Entra ID Security groups based on the query result.
In ConfigMgr, you can run a CMPivot query on device collections, not just individual devices. In Intune, you can only run queries on individual devices.
Performance; running a simple query, such as the first example I specified earlier, took 10 seconds in ConfigMgr and 16 (or more) seconds in Intune for the same device.
If you need to run a large query, and if performance is a factor, run it from the ConfigMgr console on the primary site server.
KQL allows rendering charts. However, rendering charts is currently not supported in Intune.
Troubleshooting CMPivot for devices uploaded to the Admin Center
This troubleshooting section pertains to devices uploaded to the Intune Admin Center, on which the CMPivot queries are executed directly from Intune.
Relevant log files:
CMGatewayNotificationWorker.log
CMGatewaySyncUploadWorker.log
BGBServer.log
CcmNotificationAgent.log
View this MS Docs article to learn more about the log description and location.
The article linked above describes the workflow in detail, as well as common and known issues.
Issue example:
Error: Unable to get device information. Make sure Azure AD and AD user discovery are configured and the user is discovered by both. Verify that the user has proper permissions in Configuration Manager.
Although the error message is self-explanatory, we can check the CMGatewayNotificationWorker.log. The log shows us a (401) Unauthorized exception message:
To resolve the issue, ensure your account has the necessary permissions for both Intune and ConfigMgr. Now, I’ll hand it over to Rudy to find out what DeviceQuery can bring to the table.
Device Query
Unlike CMPivot, which is a ConfigMgr tool, the DeviceQuery feature in Intune leverages the power of Microsoft Graph and KQL for querying device data directly from the Intune portal.
We will explain how Device Query brings a new dimension to real-time data querying, which you can use to troubleshoot a single device.
Prerequisites and Permissions
Before diving into Device Query, ensure Intune manages your devices, and as always, you need to make sure you have the necessary licensing in place:
The Intune Advanced Analytics Add-on or
Microsoft Intune Suite
Please Note: Device Query is not the same as Device Inventory, which was recently announced!
Device Query is for troubleshooting, Device Inventory is a reporting tool.
Device Query is not a free feature, Device Inventory will be a core feature!
CMPivot vs Device Query
CMPivot, IntunePivot, and DeviceQuery all sound similar, don’t they? But if we look closer at the IntunePivot DLL located in the Intune Management Extension folder, we might discover something interesting.
As shown above, the Device Query code mentions its father, CMPivot, quite often. So, if you are familiar with CMPivot, Device Query isn’t that different. Shall we look at how we could use Device Query to start troubleshooting a device?
Using Device Query
To start troubleshooting a device, we need to navigate to the Intune Portal → Devices → All Devices. Select a device to run your query. DeviceQuery utilizes the Windows Push Notification service and KQL to fetch real-time data.
In the example below, I will query the LocalUserAccount property to find out which local users are created on this single device.
So, what happens when I press the magic “ run” button? Let me start with a simple overview, which I will explain in detail.
This is what happens when you click the “run” button to initiate a KQL (Kusto Query Language) device query.
1. The device query will kick off the createquery Graph command.
2. Intune to WNS: The Intune service sends a push notification request to the Windows Notification Service. (WNS)
3. WNS to Device: WNS processes the notification and delivers it to the specified device.
4.PushNotification-Platform Event Log: Upon receiving the push notification, the device logs this event in the PushNotification-Platform event log, specifically event 1225, indicating that the WNP (Windows Notification Platform) Transport Layer received a command.
5. Intune Management Extension (IME): The IME on the device receives the notification and starts processing the base64 encrypted device query.
6. KQL Query Execution: The device executes the KQL query to gather the required WMI data.
7. IME to Intune: Once the device query is executed, the IME sends the results back to the Intune service.
8.Result Processing: Intune processes and prepares the results for display in the Intune console.
With the results being shown in the Intune portal, the device query’s job is done.
Additional Device Queries
For additional KQL queries beyond the localuseraccount query used in our example, please visit this GitHub page.
Limitations of Device Query
While Device Query is powerful, it also has some limitations
Scope: Queries can be run only on individual devices, not device groups.
The WindowsRegistry entity: fails to return the RegistryKey for root.
128 KB: The result string of any query is limited to 128 KB. If your query generates a result longer than this, it will be truncated. An error message will indicate the number of rows that have been truncated.
Max Amount of queries: You are allowed to send up to 15 queries per minute. If you receive a “query limit exceeded” error, please wait one minute before trying again.
Max Amount of Characters: Each query can be up to 2048 characters long. If you receive a “query too long” error, shorten your query to meet the character limit and try again.
Performance: Queries might take longer due to the nature of cloud processing and the dependency of the WNS infrastructure.
Despite these funny limitations, Device Query in Intune offers a modern, flexible approach to device management and real-time data insights. These insights could be very valuable when you need to troubleshoot the device but how to troubleshoot Device Query itself?
Troubleshooting Device Query
When you run a KQL device query and encounter the error message, “an error occurred. Try running the query again,” you are in for some fun!
The message itself provides little information on the cause. To troubleshoot this issue, we need to switch to the device itself.
Troubleshooting Steps
Step 1: Verify Push Notification Services (WNS)
Step 2: Check the Intune Management Extension Notification error message and Channel Address:
If the ChannelAddress is missing, the Intune Management Extension didn’t successfully subscribe to the WNS channel.
The Error Message could show you some more details
Step 3: Check PushNotification-Platform Event Log: Look for event 1225, which indicates that the WNP Transport Layer received a command.
If the PushNotifications event log doesn’t show you anything, the push notification wasn’t delivered to your device.
Step 4: Validate Cloud Notification Policies: Check the DisallowCloudNotification Policy:
If it shows you the above DWORD, your device will not receive push notifications, and Device Query will not work with it!
Step 5: Have patience! Wait 10 minutes after booting the device before performing a device query. If the Intune Management Extension log doesn’t mention the fact that it registered the channel, it could take some more minutes before Device Query can be used.
With this Device Query troubleshooting guide finished, it’s now time for the final CMPivot vs. Device Query conclusion.
Conclusion
CMPivot and Device Query may look similar, but each serves its specific environment—CMPivot for ConfigMgr and Device Query for Intune. Both tools provide powerful real-time data querying capabilities, helping IT admins troubleshoot and manage devices efficiently. Depending on your situation, use CMPivot for on-premises setups and Device Query for cloud-native environments to ensure optimal device management and troubleshooting.