5 ( 21 )

SCCM CMPivot has been introduced in SCCM 1806 and it’s making its way to be 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.

When we began using CMPivot, we were a bit lost. We are pretty comfortable with various programming language but CMPivot uses a subset of the Azure Log Analytics 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 Uppercase letter, Where is not the same as where. If you use an “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 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 over time 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

Description Query List all Active directory user that are administrator of their machine Administrators | where (ObjectClass == 'User') | where (PrincipalSource == 'ActiveDirectory') List on which machine an admin is administrator Administrators | where (Name == 'DOMAIN\\USERNAME')

Count application crash by devices AppCrash | summarize dcount( Device ) by FileName List application crash on a specific device AppCrash | where (Device == 'DeviceName') List all Auto Start Software on a specific device AutoStartSoftware | where (Device == 'xx') List a specific Autostart software AutoStartSoftware | where (Product == 'ProductName') Count all Bios version Bios | summarize dcount( Device ) by Version Find a specific device based on a serial number Bios | where (SerialNumber == 'xx') Find a specific device based on bios version Bios | where (Version == 'xx') 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 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') 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 Dell devices based on Manufacturer Device | where (Manufacturer like 'Dell')

List all HP devices based on Manufacturer Device | where (Manufacturer like 'HP')

Count devices by Manufacturer Device | summarize dcount( Device ) by Manufacturer

Count devices by Model Device | summarize dcount( Device ) by Model Search a specific disk based on serial number Disk | where (Description == 'Local Fixed Disk') | where (VolumeSerialNumber == 'YourNumber') List all C:\ disk information from all devices Disk | where (Description == 'Local Fixed Disk') | where (Name == 'C:') 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 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 Information about a specific file File('c:\\path\\file.exe') Information about a specific file on a specific computer File('c:\\path\\file.exe')| where (Device == ''DeviceName') Active file share information excluding Administrative Shares (Share$) FileShare | where (Type == 0) Active file share information on a specific device FileShare | where (Device == ''DeviceName') 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) List installed applications on a specific device InstalledSoftware | where (Device == ''DeviceName') List a specific installed applications InstalledSoftware | where (ProductName == 'YourProductName')

List a installed applications of a specific publisher InstalledSoftware | where (Publisher == 'YourPublisherName') List all Ethernet address that are up IPConfig | where ((InterfaceAlias like 'Ethernet') and (Status == 'Up'))



List a device based on it's IPv4 address IPConfig | where (IPV4Address == '192.168.1.1') Count device with a specific OS version OS | summarize countif( (Version == '10.0.17134') ) by Device | where (countif_ > 0) OS information on a specific device

OS | where (Device == 'DeviceName') 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 devices with Windows 10 OS | where (Version like '10%') List all devices with Windows 7 OS | where (Version like '6.1%') List a specific process Process | where (Name == 'ProcessName.exe')

List all process from a specific device Process | where (Device == 'DeviceName') List all values for a specific HKEY_LOCAL_MACHINE registry key

Registry('hklm:\\YOUR\\REGISTRY\\KEY')

List all values for a specific HKEY_CURRENT_USER registry key Registry('hkcu:\\YOUR\\REGISTRY\\KEY') List all Services on a specific machine Service | where (Device == 'DeviceName')



List machines with a specific running service Service | where (Name == 'ServiceName') | where (State == 'Running')

List machines with a specific stopped service Service | where (Name == 'ServiceName') | where (State == 'Stopped') List SMB Configuration on a specific device SMBConfig | where (Device == 'DeviceName')



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 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)

A software update applicable but not installed on a specific device SoftwareUpdate | where (Device == 'DeviceName')



Rendering data using Render operator

You can also display the data visually using the Render operator at the end of the query. ( Exemple :

Device | summarize dcount( Device ) by Manufacturer|

render barchart

The Render operator support 4 type of charts: Piechart, barchart , columnchart and timechart

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

Barchart exemple :

Piechart exemple :

Share this Post

How useful was this post? Click on a star to rate it!







Submit Rating Average rating 5 / 5. Vote count: 21 No votes so far! Be the first to rate this post.

Founder of System Center Dudes. Based in Montreal, Canada, Senior Microsoft SCCM Consultant, 5 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 Intunes deployments.

[ratings]