November 11, 2021

CMPivot Examples

CMPivot is available in the Configuration Manager console or as a standalone app which you can install from <site install path>\tools\CMPivot\CMPivot.msi.

CMPivot allows you to query data from devices in real-time. This can be very useful in scenarios where you need to find out how a certain configuration is or what the value of a setting is.

Here are a few examples which i often use and adjust from as a starting point.

Find Content in a File

FileContent('%windir%\\ccm\\logs\\locationservices.log') 
| where Content like '%Distribution Point%' 
| project Device, Content 

Find Value of a Registry Key

Registry('HKLM:/SOFTWARE/Microsoft/CCM/Ccm*') 
| where Property == 'ProvisioningMode' 

Find the Version of a File

File('C:\\program files\\Windows Defender\\msmpeng.exe') 
| project Device, Version 

Find Entries in Application EventLog Which Matches a Certain Source

EventLog('Application') 
| where Source like 'CTG-UpdateFrontEnd' 
| summarize dcount(Device) by Message 
| render barchart 

Find all devices where the Windows Defender service is stopped, Defender Install path is C:\Program Files 

Service | where (Name == 'WinDefend')  
| where (state == 'stopped')  
| join Registry('HKLM://SOFTWARE/Microsoft/Windows Defender')  
| where Property == 'InstallLocation' and Value like 'C:\Program Files%'