List of SCCM CMPivot Query

Benoit LecoursSCCM4 Comments

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 a list of SCCM CMPivot Query.

SCCM CMPivot Query

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

SCCM CMPivot Query

  • 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 Exemples

DescriptionQuery
List all Active directory user that are administrator of their machineAdministrators | where (ObjectClass == 'User') | where (PrincipalSource == 'ActiveDirectory')
List on which machine an admin is administratorAdministrators | where (Name == 'DOMAIN\\USERNAME')
Count application crash by devicesAppCrash | summarize dcount( Device ) by FileName
List application crash on a specific deviceAppCrash | where (Device == 'DeviceName')
List all Auto Start Software on a specific deviceAutoStartSoftware | where (Device == 'xx')
List a specific Autostart softwareAutoStartSoftware | where (Product == 'ProductName')
Count all Bios versionBios | summarize dcount( Device ) by Version
Find a specific device based on a serial numberBios | where (SerialNumber == 'xx')
Find a specific device based on bios versionBios | where (Version == 'xx')
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
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 destinationConnection | where (Server == ''ServerName')
List all Microsoft devices based on ManufacturerDevice | where (Manufacturer like 'Microsoft')

List all Lenovo devices based on ManufacturerDevice | where (Manufacturer like 'Lenovo')
List all Dell devices based on ManufacturerDevice | where (Manufacturer like 'Dell')
List all HP devices based on ManufacturerDevice | where (Manufacturer like 'HP')
Count devices by ManufacturerDevice | summarize dcount( Device ) by Manufacturer
Count devices by ModelDevice | summarize dcount( Device ) by Model
Search a specific disk based on serial numberDisk | where (Description == 'Local Fixed Disk') | where (VolumeSerialNumber == 'YourNumber')
List all C:\ disk information from all devicesDisk | where (Description == 'Local Fixed Disk') | where (Name == 'C:')
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
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
Information about a specific fileFile('c:\\path\\file.exe')
Information about a specific file on a specific computerFile('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 deviceFileShare | where (Device == ''DeviceName')
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)
List installed applications on a specific deviceInstalledSoftware | where (Device == ''DeviceName')
List a specific installed applicationsInstalledSoftware | where (ProductName == 'YourProductName')
List a installed applications of a specific publisherInstalledSoftware | where (Publisher == 'YourPublisherName')
List all Ethernet address that are upIPConfig | where ((InterfaceAlias like 'Ethernet') and (Status == 'Up'))

List a device based on it's IPv4 addressIPConfig | where (IPV4Address == '192.168.1.1')
Count device with a specific OS versionOS | 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 OSOS | where (OSArchitecture == '64-bit')
List all device with 32-bit OSOS | where (OSArchitecture == '32-bit')
List all devices with Windows 10OS | where (Version like '10%')
List all devices with Windows 7OS | where (Version like '6.1%')
List a specific processProcess | where (Name == 'ProcessName.exe')
List all process from a specific deviceProcess | 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 keyRegistry('hkcu:\\YOUR\\REGISTRY\\KEY')
List all Services on a specific machineService | where (Device == 'DeviceName')

List machines with a specific running serviceService | where (Name == 'ServiceName') | where (State == 'Running')
List machines with a specific stopped serviceService | where (Name == 'ServiceName') | where (State == 'Stopped')
List SMB Configuration on a specific deviceSMBConfig | where (Device == 'DeviceName')

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 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 deviceSoftwareUpdate | where (Device == 'DeviceName')

 

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 5.00 out of 5)
Loading...

Share 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.

4 Comments on “List of SCCM CMPivot Query”

  1. 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?

  2. Just wanted to let you know that there is a small typo in this post.
    SCCM CMPIVOT QUERY EXEMPLES

  3. 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’)

Leave a Reply

Your email address will not be published. Required fields are marked *