SCCM CMPivot has been introduced in SCCM 1806 and it’s making its way to being a pretty useful addition. If you are not familiar with this new feature, you can read about it in our previous post which describes how to use it. The goal of this post is to give you a list of SCCM CMPivot Query Examples. From there you can get creative to create more complex CMPivot queries.

SCCM CMPivot Query Examples

SCCM CMPivot explanation

CM Pivot is a feature within SCCM that enables administrators to run queries on devices in real time, providing instant insights into various aspects of the managed environment. This feature is particularly valuable for troubleshooting, compliance monitoring, and gathering information on a large scale without causing disruptions to the network.

When we began using CMPivot, we were a bit lost. We are pretty comfortable with various programming languages but CMPivot uses the Kusto Query language. data flow model for the tabular expression statement which was new for us.

The official Microsoft documentation states :

The typical structure of a tabular expression statement is a composition of client entities and tabular data operators (such as filters and projections). The composition is represented by the pipe character (|), giving the statement a very regular form that visually represents the flow of tabular data from left to right. Each operator accepts a tabular dataset “from the pipe”, and additional inputs (including other tabular data sets) from the body of the operator, then emits a tabular data set to the next operator that follows:

entity | operator1 | operator2 | …

SCCM CMPivot Query Catches

This results in a pretty simple language but there are a few catches that I learned :

  • Watch out for the Uppercase letters, Where is not the same as where. If you use a “W”, you’ll end up with a Failed to parse query error
SCCM CMPivot Query Examples
  • At any time you can use | project Manufacturer, Model at the end of any query to display only the desired column  (Manufacturer, Model in this case)

After a while of playing with it, we thought it would be useful to share a list of queries that we build. Here’s the SCCM CMPivot Query list, feel free to share your own and as in my other Set of Operational Collection script, this list will evolve so come back often to see that new addition we’ll make.

We hope it helps you adopt this new feature.

SCCM CMPivot Query Examples

The latest queries were added on March 28th, 2024. (In bold)

DescriptionQuery
Specific Installed Software on a specific machineInstalledSoftware | where (ProductName == '7-Zip') | where (Device == 'CM01')
Search a specific disk based on serial numberDisk | where (Description == 'Local Fixed Disk') | where (VolumeSerialNumber == 'YourNumber')
OS information on a specific device
OS | where (Device == 'DeviceName')
Machines connected to a specific domainDevice | where Domain == ('xxx')
List SMB Configuration on a specific deviceSMBConfig | where (Device == 'DeviceName')

List on which machine an admin is administratorAdministrators | where (Name == 'DOMAIN\\USERNAME')
List machines with a specific stopped service Service | where (Name == 'ServiceName') | where (State == 'Stopped')
List machines with a specific running service Service | where (Name == 'ServiceName') | where (State == 'Running')
List installed applications on a specific deviceInstalledSoftware | where (Device == ''DeviceName')
List application crash on a specific deviceAppCrash | where (Device == 'DeviceName')
List all values for a specific HKEY_LOCAL_MACHINE registry folder
Registry('hklm:\\YOUR\\REGISTRY\\KEY')
List all Services on a specific machineService | where (Device == 'DeviceName')

List all process from a specific deviceProcess | where (Device == 'DeviceName')
List all Microsoft devices based on ManufacturerDevice | where (Manufacturer like 'Microsoft')

List all Lenovo devices based on ManufacturerDevice | where (Manufacturer like 'Lenovo')
List all HP devices based on ManufacturerDevice | where (Manufacturer like 'HP')
List all Ethernet address that are upIPConfig | where ((InterfaceAlias like 'Ethernet') and (Status == 'Up'))

List all devices with Windows 10OS | where (Version like '10%')
List all devices with Windows 7OS | where (Version like '6.1%')
List all device with 64-bit OSOS | where (OSArchitecture == '64-bit')
List all device with 32-bit OSOS | where (OSArchitecture == '32-bit')
List all Dell devices based on ManufacturerDevice | where (Manufacturer like 'Dell')
List all C:\ disk information from all devicesDisk | where (Description == 'Local Fixed Disk') | where (Name == 'C:')
List all Auto Start Software on a specific deviceAutoStartSoftware | where (Device == 'xx')
List all Active directory user that are administrator of their machineAdministrators | where (ObjectClass == 'User') | where (PrincipalSource == 'ActiveDirectory')
List a specific processProcess | where (Name == 'ProcessName.exe')
List a specific installed applications InstalledSoftware | where (ProductName == 'YourProductName')
List a specific Autostart softwareAutoStartSoftware | where (Product == 'ProductName')
List a installed applications of a specific publisherInstalledSoftware | where (Publisher == 'YourPublisherName')
List a device based on it's IPv4 addressIPConfig | where (IPV4Address == '192.168.1.1')
List 50 last lines of a specific SCCM log file on a specific computerCcmLog('CCMLogName.log') | where (Device == 'DeviceName') | order by DateTime desc | project Device, LogText, DateTime
List 50 last lines of a specific SCCM log fileCcmLog('CCMLogName') | order by DateTime desc | project Device, LogText, DateTime
Last 50 events from the System event logEventLog('System') | order by DateTime desc
Last 50 events from the Security event logEventLog('Security') | order by DateTime desc
Last 50 events from the Application event log from a specific computerEventLog('Application') | where (Device == 'DeviceName') | order by DateTime desc
Last 50 events from the Application event logEventLog('Application') | order by DateTime desc
Information about a specific file on a specific computerFile('c:\\path\\file.exe')| where (Device == ''DeviceName')
Information about a specific fileFile('c:\\path\\file.exe')
Find the Google Chrome Home PageRegistry('hklm:\software\polices\google\chrome') | where Property == ('Homepagelocation')
Find a specific device based on bios versionBios | where (Version == 'xx')
Count of application installed on the deviceInstalledSoftware | summarize dcount( Device ) by ProductName
Count Devices with a specific application
InstalledSoftware | summarize countif( (ProductName == 'YourProductName') ) by Device | where (countif_ > 0)
Count devices by ModelDevice | summarize dcount( Device ) by Model
Count devices by ManufacturerDevice | summarize dcount( Device ) by Manufacturer
Count device with a specific software update applicable but not installed on the device (by KB Number)SoftwareUpdate | summarize countif( (KBArticleIDs == 'KB0000000') ) by Device | where (countif_ > 0)
Count device with a specific OS versionOS | summarize countif( (Version == '10.0.17134') ) by Device | where (countif_ > 0)
Count application crash by devicesAppCrash | summarize dcount( Device ) by FileName
Count all installed software that does not have Microsoft in the product nameInstalledSoftware | where ProductName !like '%Microsoft%' | summarize dcount(Device) by ProductName
Count all device with SMB1 enabledSMBConfig | summarize countif( (EnableSMB1Protocol == true) ) by Device | where (countif_ > 0)
Count all device with SMB1 disabledSMBConfig | summarize countif( (EnableSMB1Protocol == false) ) by Device | where (countif_ > 0)
Count all Bios versionBios | summarize dcount( Device ) by Version
Boot Time Bar Chart per deviceSystemBootData | summarize avg( BootDuration / 1000 ) by Device | order by avg_ asc | render barchart
All VMWare DevicesDevice | where ManuFacturer == 'VMware, Inc.'
Active Tcp connection in or out of the device to a specific destinationConnection | where (Server == ''ServerName')
Active Tcp connection in or out of a specific device to a specific destination'Connection | where (Device == ''DeviceName') | where (Server == ''ServerName')
Active file share information on a specific deviceFileShare | where (Device == ''DeviceName')
Active file share information excluding Administrative Shares (Share$)FileShare | where (Type == 0)
A software update applicable but not installed on a specific deviceSoftwareUpdate | where (Device == 'DeviceName')
OS Installed Feature (AD, DHCP, DNS...)OptionalFeature | where (Caption == 'Feature Name') | where InstallState == 1
Office Add-in per machine with usernameOfficeAddin | where ProductName like 'Addin Name' | join Device | project UserName, FileName, Device, ProductName, ProductVersion
List devices with firmware ready for UEFI and SecureBootFirmware | where (UEFI == true and SecureBoot == true)
List all Windows 10 and Windows 11 version in a ChartOperatingSystem | where ProductType == 1 and Caption contains '10' | project OSEdition=case(
BuildNumber == '10240', 'Windows 10 1507',
BuildNumber == '10586', 'Windows 10 1511',
BuildNumber == '14393', 'Windows 10 1607',
BuildNumber == '15063', 'Windows 10 1703',
BuildNumber == '16299', 'Windows 10 1709',
BuildNumber == '17134', 'Windows 10 1803',
BuildNumber == '17763', 'Windows 10 1809',
BuildNumber == '18362', 'Windows 10 1903',
BuildNumber == '18363', 'Windows 10 1909',
BuildNumber == '19041', 'Windows 10 2004',
BuildNumber == '19042', 'Windows 10 20H2',
BuildNumber == '19043', 'Windows 10 21H1',
BuildNumber == '19044', 'Windows 10 21H2',
BuildNumber == '19045', 'Windows 10 22H2',
BuildNumber == '22000', 'Windows 11 21H2', BuildNumber)
| summarize count() by OSEdition | render columnchart with(title='Windows 10-11 versions', ytitle='Count')
List all PCs with a specific event IDEventLog('System') | summarize countif( (EventID == 1234) ) by Device | where (countif_ > 0)
Installed software piechart perversionInstalledSoftware | where (ProductName == 'Your Software') | summarize count() by ProductVersion | render piechart
Free space on a local hard diskLogicalDisk | where (Description == 'Local Fixed Disk') | project DeviceID, FreeSpace | order by FreeSpace desc
Find device without a specific fileDevice | join kind=leftouter (File('C:\windows\notepad.exe')) | where isnull(FileName)
Find a specific file and versionFile('c:\windows\notepad.exe') | project Device, FileName, Version
Find a machine having a specific serial numberBios | where SerialNumber == ('your serial')
Display file and version using a chartFile('C:\windows\notepad.exe') | summarize count() by Version | order by version | render columnchart with(title='Notepad versions', ytitle='Count')
Count the different OS versions of devices rendered as a bar chartOS | summarize dcount (Device) by Caption | render barchart
List the value of a specific Registry Key Ex : Registry('hklm:\\system\\CurrentControlSet\\Control\\LSA') | where Property == 'lsapid'
List all machines that have not a specific registry key Device | join kind=leftouter (Registry('hklm:\\YOUR\\REGISTRY\\KEY') | where Property == ('YOURKEY')) | where isnull(Property) | project Device
TPM is activated and enabled on your computers TPM | where IsEnabled_InitialValue == true and IsActivated_InitialValue == true
List all machine where Bitlocker is enabled BitLocker | where ProtectionStatus == 1
List all machines with low free space (5gb) LogicalDisk | where FreeSpace < 5000
Coutn Device per ManufacturerDevice | summarize dcount( Device ) by Manufacturer

Rendering data using the Render operator

You can also display the data visually using the Render operator at the end of the query. (Example :
Device | summarize dcount( Device ) by Manufacturer|
render barchart

The Render operator supports 4 types of charts:  Piechart,barchart,columnchart and timechart

SCCM CMPivot Query Examples

The Render operator supports only two properties in the source table and the last property must be a number.

Barchart exemple :

SCCM CMPivot Query Examples

Piechart exemple :

SCCM CMPivot Query Examples
Comments (17)

aziz

02.15.2024 AT 01:51 AM
thank you for perfect work, any way to find query to get (PC name, Users, Windows Version, Office Version, IP, MAC) for all PCs once without repeat PC Name (meaning if there many users profile do not bring pervious information for each profile) so report can be smaller. thank you for your help

Blankman

03.12.2020 AT 03:03 PM
How to query a large collection for PCs that DO NOT have a specific software installed?

dj3094

02.26.2020 AT 10:23 AM
Great post, Is there any query on How to get device information, SMB enabled or disbled and OS?

How to

01.22.2020 AT 05:45 PM
SoftwareUpdate | summarize countif( (KBArticleIDs == 'KB0000000') ) by Device | where (countif_ > 0) Can I add more than 1 kb to this query?

Al

01.16.2020 AT 07:14 PM
Is there a method to concatenate two fields for summarization? I am wanting a unique count of a computer model and the CPU contained

Mike Mc

12.20.2019 AT 10:33 AM
How do you run an EXE to display results? I get failed to parse query. The exe i'm trying to run is: powermt display paths

Adriana Perez

10.29.2019 AT 04:37 PM
Is there way to query the collection for installed software by ProductName and ProductVersion. I tried this and it failed. InstalledSoftware | summarize dcount( Device ) by ProductName | where (ProductName == ‘Adobe Shockwave Player 12.3’) | where (ProductVersion == ‘12.3.1.201’) what about the last 3 versions

svariell

10.21.2019 AT 11:41 AM
What should I be querying to get a list of "pending restart" devices?

Aaron Stom

09.26.2019 AT 01:27 PM
Trying to add Serial number to my query, can anyone help? Device | where (Model == '20ELS0QT00') | summarize by Device, UserName

Kevin Severud

05.23.2019 AT 02:26 PM
I like this one: AppCrash | summarize dcount( Device ) by FileName

Robert Paulson

03.07.2019 AT 07:52 PM
Hello, I'm receiving an error message which states "Unsupported version of Powershell" when all of the targeted machines in the collection have at least 5.1 installed.

Postive.Pete

10.23.2018 AT 02:53 PM
Is there way to query the collection for installed software by ProductName and ProductVersion. I tried this and it failed. InstalledSoftware | summarize dcount( Device ) by ProductName | where (ProductName == 'Adobe Shockwave Player 12.3') | where (ProductVersion == '12.3.1.201')

Nick

11.07.2018 AT 10:33 PM
You need to include ProductVersion in the 'summarize by' section seeing as you want to filter with it. Try this: InstalledSoftware | summarize dcount( Device ) by ProductName, ProductVersion | where (ProductName == ‘Adobe Shockwave Player 12.3’) | where (ProductVersion == ‘12.3.1.201’)

Justin

10.03.2018 AT 03:54 PM
The support for operators seems to be inconsistent. For instance, "OS | where (Version like '10.%')" works fine, but "InstalledSoftware | where (ProductVersion like '10.%')" is causing query failures. In fact, I can't get the "like" operator to work at all for filtering the InstalledSoftware dataset. Am I missing something?

Ty Bazemore

10.01.2018 AT 10:33 AM
Thank you so much, super valuable and I have logged onto and registered with the site!! Thank You.

Gerry Borger

09.29.2018 AT 08:22 AM
Just wanted to let you know that there is a small typo in this post. SCCM CMPIVOT QUERY EXEMPLES

tstolswo

09.27.2018 AT 04:54 PM
Great list of queries! A few of them, below, have an extra " ' " located: (Device = 'DeviceName'), (Server == 'ServerName'), and (Server == 'ServerName') Active Tcp connection in or out of a specific device to a specific destination' Connection | where (Device == ''DeviceName') | where (Server == ''ServerName') Active Tcp connection in or out of the device to a specific destination Connection | where (Server == ''ServerName')