SCCM CMPivot Architecture and Sample Queries

Let’s check SCCM CMPivot Architecture and Sample Queries in this post. CMPivot allows you to quickly assess the state of devices in your environment and take action.

Fast channel architecture changed the entire behavior of SCCM. Now it’s not at all a slow-moving server(SMS). You can make it run as fast as you can.

The SCCM CMPivot Queries can be based on supported entities of each version of SCCM. Entities – this is what Microsoft calls the querying objects of each SCCM client.

More details about SCCM 1810 Improvements and what is new with CMPivot are available below. Are you interested to see the first version of CMPivot? Following is my post-SCCM CB Preview version 1805.

What is CMPivot

The CMPivot is a new in-console and standalone utility in ConfigMgr that now provides access to the real-time state of devices in your environment. You can run CMPivot as a standalone tool as well.

• SCCM 2403 New Key Features and Improvements
• Best Guide to Deploy Windows 11 22H2 using SCCM Task Sequence | ConfigMgr

Prerequisite of SCCM CMPivot

Ensure you are running the latest version of the SCCM client and CMPivot (SCCM console). SCCM security permissions are required to run CMPivot, as Microsoft documented here.

Also, SCCM client devices should require PowerShell version 5.0. The Start CMPivot action doesn’t appear in the console when connected to an SCCM CAS site.

How does SCCM CMPivot Works in the Background?

The architecture of CMPivot is based on fast channel architecture. The steps explained below help to understand very high-level CMPivot architecture flow.

1. CMPivot sends queries to SCCM clients using SCCM Fast Channel
2. SCCM Clients return results via the similarly quick state message system
3. The CMPivot results are temporarily stored in the database (until the CMPivot window is open?)
4. A CMPivot Query will try to connect to machines that are not online for an hour. This retry happens only when the CMPivot window is open.

The following picture is the fast channel’s architecture flow, which I explained in the fast channel notification post.

Video Tutorial – SCCM CMPivot Guide Overview

How to Run Query in SCCM CMPivot?

1. Go to the Assets and Compliance workspace in the SCCM CB 1806 or later console and select Device Collections. Select a target collection, and click Start CMPivot in the ribbon to launch the tool.
2. CMPivot gets open as a different window. The CMPivot pane on the left lists the entities available to clients. Some entities rely upon WMI, while others use PowerShell to get data from clients.
   • Right-click an entity for the following actions:

Insert: Add the entity to the query at the current cursor position. The question doesn’t automatically run. This action is the default when you double-click an entity. Use this action when building a query.

Query all: Run a query for this entity, including all properties. Use this action to query for a single entity quickly.

Query by device: Run a question for this entity and group the results. For example, Disk | summarize dcount( Device ) by Name

What are the Actions you can launch from SCCM CMPivot?

Apart from querying the online devices in almost real-time, what other actions can you take directly from CMPivot? Well, you can see some handy options available for you.

The SCCM CMPivot results pane displays the data returned by active clients for the query, and you have some right-click options, as I mentioned below.

• The available columns vary based on the entity and the query.
• Click a column name to sort the results by that property.
• Right-click on any column name to group the results by the same information in that column or sort the results.
• Right-click on a device name to take the following additional actions on the device.

1. Create a direct membership device collections
2. Export the CMPivot query report results to CSV
3. Export the CMPivot query report results to Clipboard
4. Run Remediation scripts to selected devices (SCCM client)
5. Take Remote Control of a device from CMPivot
6. Take Resource Explorer of an SCCM client device from CMPivot
7. Select a device and do Pivot to another entity to drill down the issues

SCCM CMPivot Troubleshooting & Log files

OK. So the communication channel of CMPivot is the same as SCCM fast channel. I recommend reading my previous post to perform deep-dive troubleshooting of CMPivot issues.

Apart from that, log files are good friends of SCCM admins all the time, and for CMPivot also, it’s the same.

Make sure your basics are correct or not. Check out the request-id at the right bottom side of CMPivot windows. This id will help you to troubleshoot via log files.

Check out the collection from which you initiated the CMPivot at the bottom right side of the CMPivot windows.

Following are some of the log files you want to look at when you have trouble with CMPivot.

Server-side CMPivot Log	Client-side CMPivot Log
SmsProv.log bgbServer.log StateSys.log	CcmNotificationAgent.log Scripts.log StateMessage.log

• On the client, by default in C:\Windows\CCM\logs:
  • Scripts.log
  • CcmMessaging.log
• On the MP, by default in C:\SMS_CCM\Logs: MP_RelayMsgMgr.log
• On the site server, by default, in C:\Program Files\Configuration Manager\Logs: SMS_Message_Processing_Engine.log

Any clients that returned Total, Failed, Offline – Query completed on 3 of 5 clients (2 clients offline and 0 failure)

Client Operation ID	Collection Name	The total number of rows in the results pane
id(16780221)	All Desktop and Server Clients	1 objects

List of Entities Supported by SCCM CMPivot

CMPivot Entities – these entities are what Microsoft calls the querying objects of each SCCM client. I hope this list will grow with newer releases of SCCM CB.

• Administrators – Members of the local administrator’s group
• app crash – Recent application crash reports
• AutoStartSoftware – Software that starts automatically with, or immediately after, the operating system
• Bios – System BIOS information
• CcmLog() – Up to the last 50 lines from a Ccm Log file
• Connection – An active TCP connection in or out of the device
• Device – Basic information about the device
• Disk – Local storage device information on a computer system running Windows
• EventLog() – Up to the last 50 events from a Windows event log
• File() – Information about a specific file
• FileShare – Active file share information
• InstalledSoftware – An application installed on the device
• IPConfig – Gets network configuration, including usable interfaces, IP addresses, and DNS servers
• OS – Basic information about the operating system
• Process – A process on an operating system
• Registry() – All values for a specific registry key
• Service – A service on a computer system running Windows
• SMBConfig – SMB Configuration of a device
• SoftwareUpdate – A software update applicable but not installed on the device
• User – A user account with an active connection to the device

SCCM CMPivot Entity

The following are the entities that can be queried from SCCM 1810 CMPivot. Most of the WMI classes are included in the SCCM 1810 production version. Also, there are some other improvements:

1. CMPivot gives an option Save Favorite queries
2. On the Query Summary tab, select the count of Failed or Offline devices, and then choose the option to Create Collection.

Sample SCCM CMPilot Query

Sample queries you can try with SCCM CMPivot.

Disk | summarize dcount( Device ) by Name OS | summarize countif( (Version == ‘10.0.17134’) ) by Device | where (countif_ > 0) OS | summarize countif( (Version == ‘10.0.17134’) ) by Device | where (countif_ == 0) | project Device Service | summarize dcount( Device ) by Name Service | where (Name == ‘Browser’) | summarize count() by Device Bios | summarize countif( (Version == ‘LENOVO – 1140’) ) by Device | where (countif_ > 0) Disk | where (Description == ‘Local Fixed Disk’) | where isnotnull( FreeSpace ) | order by FreeSpace asc

Let’s find out the recently used apps from online devices using SCCM CMPivot.

CCMRecentlyUsedApplications
| where (LastUsedTime > ago(5h))
| project CompanyName, ProductName, ProductVersion, LastUsedTime

Now, let’s look at Windows 11 Device start time using the SCCM CMPivot query.

SystemBootData
| project Device, SystemStartTime, BootDuration, OSStart=EventLogStart, GPDuration, UpdateDuration
| order by SystemStartTime desc

Gets the status of antimalware software installed on the Windows 10 or Windows 11 devices using the following query.

EPStatus
| project Device, QuickScanAge=datetime_diff('day',now(),QuickScanEndTime)
| summarize DeviceCount=count() by QuickScanAge

ProcessModule details can also be queried using SCCM CMPivot.

ProcessModule('powershell')
| summarize count() by ModuleName
| order by count_ desc

CMPivot query to get the results with the chart.

OperatingSystem | summarize count() by BuildNumber | render piechart

Windows 10 and Windows 11 Devices CMPivot query

OperatingSystem | where Caption == 'Microsoft Windows 10 Enterprise'
OperatingSystem | where Caption == 'Microsoft Windows 10 Eduation'

OperatingSystem | where Caption == 'Microsoft Windows 11 Enterprise'
OperatingSystem | where Caption == 'Microsoft Windows 11 Eduation'

You can get Application Crash details from CMPviot Query

AppCrash | summarize dcount( Device ) by FileName,Version

You can get the BIOS details of Windows 10 and Windows 11 devices from the SCCM CMPivot query.

Bios | summarize dcount( Device ) by Manufacturer

You can also find the event log details and File Details of the Windows 10 devices using SCCM CMPivot.

File('%windir%\\notepad.exe')

EventLog('Security',1d)

List of Installed Software using CMPivot

This gives the count of applications installed on the device.

InstalledSoftware | summarize count( Device ) by ProductName

Total Number of Processes Running on Windows 11 System

Process | summarize dcount( Device ) by Name

Total Number of Sevices Running on Windows 11 PC

Service | summarize dcount( Device ) by Name

Software Updates SCCM CMPivot Query

Count of devices with a specific software update applicable but not installed on the device (by KB Number). Thanks to Merlin from Belgium for this query.

SoftwareUpdate | summarize countif( (KBArticleIDs == 'KB0000000') ) by Device | where (countif_ > 0)

Table of CMPivot Entity

A list of CMPivot entities is given in the below table.

Entity	Description
ActiveSyncService	ActiveSync Service
Administrators	Members of the local administrator’s group
AMTAgent	AMT Agent
AppCrash	Recent application crash reports
AppVClientApplication	AppV Client Application
AppVClientPackage	AppV Client Package
AutoStartSoftware	Software that starts automatically with, or immediately after, the operating system
BaseBoard	BaseBoard
Battery	Battery
Bios	System BIOS information
BitLocker	BitLocker
boot configuration	Boot Configuration
BrowserHelperObject	Browser Helper Object
CcmLog()	Up to the last 50 lines from a Ccm Log file
CCMRAX	CCM_RAX
CCMRecentlyUsedApplications	Recently Used Applications
CCMWebAppInstallInfo	Web Applications
CDROM	CDROM Drive
ClientEvents	Client Events
computer system	Computer System
ComputerSystemProduct	Computer System Product
ConnectedDrive	Connected Device
Connection	An active TCP connection in or out of the device
Desktop	Desktop
DesktopMonitor	Desktop Monitor
Device	Basic information about the device
Disk	Local storage device information on a computer system running Windows
DMA	DMA
DMAChannel	DMA Channel
DriverVxD	Driver – VxD
EmbeddedDeviceInformation	Embedded Device Information
Environment	Environment
EventLog()	Up to the last 50 events from a Windows event log
File()	Information about a specific file
FileShare	Active file share information
Firmware	Firmware
IDEController	IDE Controller
InstalledExecutable	Installed Executable
InstalledSoftware	An application installed on the device
IPConfig	Gets network configuration, including usable interfaces, IP addresses, and DNS servers
IRQTable	IRQ Table
Keyboard	Keyboard
LoadOrderGroup	Load Order Group
LogicalDisk	Logical Disk
MDMDevDetail	Device Information
Memory	Memory
Modem	Modem
Motherboard	Motherboard
NAPClient	NAP Client
NAPSystemHealthAgent	NAP System Health Agent
network adapter	Network Adapter
NetworkAdapterConfiguration	Network Adapter Configuration
NetworkClient	Network Client
NetworkLoginProfile	Network Login Profile
NTEventlogFile	NT Eventlog File
Office365ProPlusConfigurations	Office 365 ProPlus Configurations
OperatingSystem	Operating System
OperatingSystemRecoveryConfiguration	Operating System Recovery Configuration
OptionalFeature	Optional Feature
OS	Basic information about the operating system
PageFileSetting	Page File Setting
parallel port	Parallel Port
Partition	Disk Partitions
PCMCIA controller	PCMCIA Controller
physical memory	Physical Memory
PNPDEVICEDRIVER	PNP Device Driver
pointing device	Pointing Device
portable battery	Portable Battery
Ports	Ports
PowerCapabilities	Power Capabilities
PowerClientOptOutSettings	Power Management Exclusion Settings
PowerConfigurations	Power Configuration
PowerManagementDaily	Power Management Daily Data
PowerManagementInsomniaReasons	Power Insomnia Reasons
PowerManagementMonthly	Power Management Monthly Data
power setting	Power Settings
PrinterConfiguration	Printer Configuration
PrinterDevice	Printer Device
print jobs	Print Jobs
Process	A process on an operating system
Processor	Processor
ProtectedVolumeInformation	Protected Volume Information
Protocol	Protocol
QuickFixEngineering	Quick Fix Engineering
Registry()	All values for a specific registry key
SCSI controller	SCSI Controller
SerialPortConfiguration	Serial Port Configuration
serial port	Serial Ports
ServerFeature	Server Feature
Service	Service on a computer system running Windows
Services	Services
Shares	Shares
SMBConfig	SMB Configuration of a device
SMSAdvancedClientPorts	Configuration Manager Client Ports
SMSAdvancedClientSSLConfigurations	Configuration Manager Client SSL Configurations
SMSAdvancedClientState	Configuration Manager Client State
SMSDefaultBrowser	Default Browser
SMSSoftwareTag	Software Tag
SMSWindows8Application	Windows app
SMSWindows8ApplicationUserInfo	Windows app User Info
SoftwareShortcut	Software Shortcut
SoftwareUpdate	A software update is applicable but not installed on the device
SoundDevices	Sound Devices
SWLicensingProduct	Software Licensing Product
SWLicensingService	Software Licensing Service
SystemAccount	System Account
SystemConsoleUsage	System Console Usage
SystemConsoleUser	System Console User
SystemDevices	System Devices
SystemDrivers	System Drivers
SystemEnclosure	System Enclosure
TapeDrive	Tape Drive
TimeZone	Time Zone
TPM	TPM
TPMStatus	TPM Status
TSIssuedLicense	TS Issued License
TSLicenseKeyPack	TS License Key Pack
UninterruptiblePowerSupply	Uninterruptible Power Supply
USB controller	USB Controller
USB device	USB Device
User	A user account with an active connection to the device
USMFolderRedirectionHealth	Folder Redirection Health
USMUserProfile	User Profile Health
video controller	Video Controller
VirtualMachine	Virtual Machine
VirtualMachine64	Virtual Machine (64)
Volume	Volume
WindowsUpdate	Windows Update
WindowsUpdateAgentVersion	Windows Update Agent Version
WriteFilterState	Write Filter State

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is a Blogger, Speaker, and Local User Group HTMD Community leader. His primary focus is Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.