Here are sample CMPivot Queries that I have come across since it was introduced in 1806
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’) |