SCCM CMPivot Query Examples
Founder of System Center Dudes. Based in Montreal, Canada, Senior Microsoft SCCM Consultant, 8 times Enterprise Mobility MVP. Working in the industry since 1999. His specialization is designing, deploying and configuring SCCM, mass deployment of Windows operating systems, Office 365 and Intune deployments.
Benoit Lecours
PresidentTable of Content
Share
Get the latest insights and exclusive content delivered to your inbox
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 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
- 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)
| Description | Query |
|---|---|
| Specific Installed Software on a specific machine | InstalledSoftware | where (ProductName == '7-Zip') | where (Device == 'CM01') |
| Search a specific disk based on serial number | Disk | where (Description == 'Local Fixed Disk') | where (VolumeSerialNumber == 'YourNumber') |
| OS information on a specific device | OS | where (Device == 'DeviceName') |
| Machines connected to a specific domain | Device | where Domain == ('xxx') |
| List SMB Configuration on a specific device | SMBConfig | where (Device == 'DeviceName') |
| List on which machine an admin is administrator | Administrators | 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 device | InstalledSoftware | where (Device == ''DeviceName') |
| List application crash on a specific device | AppCrash | 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 machine | Service | where (Device == 'DeviceName') |
| List all process from a specific device | Process | where (Device == 'DeviceName') |
| List all Microsoft devices based on Manufacturer | Device | where (Manufacturer like 'Microsoft') |
| List all Lenovo devices based on Manufacturer | Device | where (Manufacturer like 'Lenovo') |
| List all HP devices based on Manufacturer | Device | where (Manufacturer like 'HP') |
| List all Ethernet address that are up | IPConfig | where ((InterfaceAlias like 'Ethernet') and (Status == 'Up')) |
| List all devices with Windows 10 | OS | where (Version like '10%') |
| List all devices with Windows 7 | OS | where (Version like '6.1%') |
| List all device with 64-bit OS | OS | where (OSArchitecture == '64-bit') |
| List all device with 32-bit OS | OS | where (OSArchitecture == '32-bit') |
| List all Dell devices based on Manufacturer | Device | where (Manufacturer like 'Dell') |
| List all C:\ disk information from all devices | Disk | where (Description == 'Local Fixed Disk') | where (Name == 'C:') |
| List all Auto Start Software on a specific device | AutoStartSoftware | where (Device == 'xx') |
| List all Active directory user that are administrator of their machine | Administrators | where (ObjectClass == 'User') | where (PrincipalSource == 'ActiveDirectory') |
| List a specific process | Process | where (Name == 'ProcessName.exe') |
| List a specific installed applications | InstalledSoftware | where (ProductName == 'YourProductName') |
| List a specific Autostart software | AutoStartSoftware | where (Product == 'ProductName') |
| List a installed applications of a specific publisher | InstalledSoftware | where (Publisher == 'YourPublisherName') |
| List a device based on it's IPv4 address | IPConfig | where (IPV4Address == '192.168.1.1') |
| List 50 last lines of a specific SCCM log file on a specific computer | CcmLog('CCMLogName.log') | where (Device == 'DeviceName') | order by DateTime desc | project Device, LogText, DateTime |
| List 50 last lines of a specific SCCM log file | CcmLog('CCMLogName') | order by DateTime desc | project Device, LogText, DateTime |
| Last 50 events from the System event log | EventLog('System') | order by DateTime desc |
| Last 50 events from the Security event log | EventLog('Security') | order by DateTime desc |
| Last 50 events from the Application event log from a specific computer | EventLog('Application') | where (Device == 'DeviceName') | order by DateTime desc |
| Last 50 events from the Application event log | EventLog('Application') | order by DateTime desc |
| Information about a specific file on a specific computer | File('c:\\path\\file.exe')| where (Device == ''DeviceName') |
| Information about a specific file | File('c:\\path\\file.exe') |
| Find the Google Chrome Home Page | Registry('hklm:\software\polices\google\chrome') | where Property == ('Homepagelocation') |
| Find a specific device based on bios version | Bios | where (Version == 'xx') |
| Count of application installed on the device | InstalledSoftware | summarize dcount( Device ) by ProductName |
| Count Devices with a specific application | InstalledSoftware | summarize countif( (ProductName == 'YourProductName') ) by Device | where (countif_ > 0) |
| Count devices by Model | Device | summarize dcount( Device ) by Model |
| Count devices by Manufacturer | Device | 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 version | OS | summarize countif( (Version == '10.0.17134') ) by Device | where (countif_ > 0) |
| Count application crash by devices | AppCrash | summarize dcount( Device ) by FileName |
| Count all installed software that does not have Microsoft in the product name | InstalledSoftware | where ProductName !like '%Microsoft%' | summarize dcount(Device) by ProductName |
| Count all device with SMB1 enabled | SMBConfig | summarize countif( (EnableSMB1Protocol == true) ) by Device | where (countif_ > 0) |
| Count all device with SMB1 disabled | SMBConfig | summarize countif( (EnableSMB1Protocol == false) ) by Device | where (countif_ > 0) |
| Count all Bios version | Bios | summarize dcount( Device ) by Version |
| Boot Time Bar Chart per device | SystemBootData | summarize avg( BootDuration / 1000 ) by Device | order by avg_ asc | render barchart |
| All VMWare Devices | Device | where ManuFacturer == 'VMware, Inc.' |
| Active Tcp connection in or out of the device to a specific destination | Connection | 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 device | FileShare | 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 device | SoftwareUpdate | where (Device == 'DeviceName') |
| OS Installed Feature (AD, DHCP, DNS...) | OptionalFeature | where (Caption == 'Feature Name') | where InstallState == 1 |
| Office Add-in per machine with username | OfficeAddin | where ProductName like 'Addin Name' | join Device | project UserName, FileName, Device, ProductName, ProductVersion |
| List devices with firmware ready for UEFI and SecureBoot | Firmware | where (UEFI == true and SecureBoot == true) |
| List all Windows 10 and Windows 11 version in a Chart | OperatingSystem | 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 ID | EventLog('System') | summarize countif( (EventID == 1234) ) by Device | where (countif_ > 0) |
| Installed software piechart perversion | InstalledSoftware | where (ProductName == 'Your Software') | summarize count() by ProductVersion | render piechart |
| Free space on a local hard disk | LogicalDisk | where (Description == 'Local Fixed Disk') | project DeviceID, FreeSpace | order by FreeSpace desc |
| Find device without a specific file | Device | join kind=leftouter (File('C:\windows\notepad.exe')) | where isnull(FileName) |
| Find a specific file and version | File('c:\windows\notepad.exe') | project Device, FileName, Version |
| Find a machine having a specific serial number | Bios | where SerialNumber == ('your serial') |
| Display file and version using a chart | File('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 chart | OS | 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 Manufacturer | Device | 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. (
Device | summarize dcount( Device ) by Manufacturer|
render barchart
The Render operator supports 4 types of charts: Piechart,
The Render operator supports only two properties in the source table and the last property must be a number.
Barchart exemple :
Piechart exemple :
Share
Get the latest insights and exclusive content delivered to your inbox
aziz
02.15.2024 AT 01:51 AMBlankman
03.12.2020 AT 03:03 PMdj3094
02.26.2020 AT 10:23 AMHow to
01.22.2020 AT 05:45 PMAl
01.16.2020 AT 07:14 PMMike Mc
12.20.2019 AT 10:33 AMAdriana Perez
10.29.2019 AT 04:37 PMsvariell
10.21.2019 AT 11:41 AMAaron Stom
09.26.2019 AT 01:27 PMKevin Severud
05.23.2019 AT 02:26 PMRobert Paulson
03.07.2019 AT 07:52 PMPostive.Pete
10.23.2018 AT 02:53 PMNick
11.07.2018 AT 10:33 PMJustin
10.03.2018 AT 03:54 PMTy Bazemore
10.01.2018 AT 10:33 AMGerry Borger
09.29.2018 AT 08:22 AMtstolswo
09.27.2018 AT 04:54 PM