CMPivot overview

Applies to: Configuration Manager (current branch)

CMPivot allows you to quickly assess the state of devices in your environment and take action. When you enter a query, CMPivot will run a query in real time on all currently connected devices in the selected collection. The data returned can then be filtered, grouped, and refined to answer business questions, troubleshoot issues in your environment, or respond to security threats. For more information about using CMPivot, see Use CMPivot.

Queries

Queries can be used to search terms, identify trends, analyze patterns, and provide many other insights based on your data. CMPivot uses a subset of the Azure Log Analytics data flow model for the tabular expression statement. 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 regular form that visually represents the flow of tabular data from left to right. Each operator accepts a tabular data set "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 | ...

In the following example, the entity is CCMRecentlyUsedApplications (a reference to the recently used applications), and the operator is where (which filter out records from its input according to some per-record predicate):

CCMRecentlyUsedApplications | where CompanyName like '%Microsoft%' | project CompanyName, ExplorerFileName, LastUsedTime, LaunchCount, FolderPath

Entities

Entities are objects that can be queried from the client. We currently support the following entities:

Entity	Description
AadStatus	Status of Microsoft Entra ID
Administrators	Members of the local administrators group
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
BitLockerEncryptionDetails	BitLocker Encryption Details
BitLockerPolicy	BitLocker Policy
BootConfiguration	Boot Configuration
BrowserHelperObject	Browser Helper Object
BrowserUsage	Browser Usage
CcmLog()	Lines within 24 hours (by default) from a Ccm Log file
CCMRAX	CCM_RAX
CCMRecentlyUsedApplications	Recently Used Applications
CCMWebAppInstallInfo	Web Applications
CDROM	CDROM Drive
ClientEvents	Client Events
ComputerSystem	Computer System
ComputerSystemEx	Computer System Ex
ComputerSystemProduct	Computer System Product
ConnectedDevice	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
EPStatus	Status of antimalware software on the computer gathered by the Get-MpComputerStatus cmdlet. Supported on Windows 10 and Server 2016, or later with defender running.
EventLog()	Events within 24 hours (by default) from an 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
NetworkAdapter	Network Adapter
NetworkAdapterConfiguration	Network Adapter Configuration
NetworkClient	Network Client
NetworkLoginProfile	Network Login Profile
NTEventlogFile	NT Eventlog File
Office365ProPlusConfigurations	Office 365 Apps Configurations
OfficeAddin	Office add-ins
OfficeClientMetric	Office Client Metric
OfficeDeviceSummary	Office Device Summary
OfficeDocumentMetric	Office document metrics
OfficeDocumentSolution	Office Document Solution
OfficeMacroError	Office Macro Error
OfficeProductInfo	Office Product Info
OfficeVbaRuleViolation	Office Vba Rule Violation
OfficeVbaSummary	Office VBA scan summary
OperatingSystem	Operating System
OperatingSystemEx	Operating System Ex
OperatingSystemRecoveryConfiguration	Operating System Recovery Configuration
OptionalFeature	Optional Feature
OS	Basic information about the operating system
PageFileSetting	Page File Setting
ParallelPort	Parallel Port
Partition	Disk Partitions
PCMCIAController	PCMCIA Controller
PhysicalDisk	PhysicalDisk
PhysicalMemory	Physical Memory
PNPDEVICEDRIVER	PNP Device Driver
PointingDevice	Pointing Device
PortableBattery	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
PowerSettings	Power Settings
PrinterConfiguration	Printer Configuration
PrinterDevice	Printer Device
PrintJobs	Print Jobs
Process	A process on an operating system
ProcessModule()	Modules loaded by specified processes
Processor	Processor
ProtectedVolumeInformation	Protected Volume Information
Protocol	Protocol
QuickFixEngineering	Quick Fix Engineering
Registry	All values for a specific registry key Starting in version 2107, Key value was added to the Registry() entity
SCSIController	SCSI Controller
SerialPortConfiguration	Serial Port Configuration
SerialPorts	Serial Ports
ServerFeature	Server Feature
Service	A 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 applicable but not installed on the device
SoundDevices	Sound Devices
SWLicensingProduct	Software Licensing Product
SWLicensingService	Software Licensing Service
SystemAccount	System Account
SystemBootData	System Boot Data
SystemBootSummary	System Boot Summary
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
USBController	USB Controller
USBDevice	USB Device
User	A user account with an active connection to the device
USMFolderRedirectionHealth	Folder Redirection Health
USMUserProfile	User Profile Health
VideoController	Video Controller
VirtualMachine	Virtual Machine
VirtualMachine64	Virtual Machine (64)
Volume	Volume
WindowsUpdate	Windows Update
WindowsUpdateAgentVersion	Windows Update Agent Version
WinEvent()	Events within 24 hours (by default) from a Windows event log
WriteFilterState	Write Filter State

Table operators

Table operators can be used filter, summarize, and transform data streams. Currently the following operators are supported:

Table operators	Description
count	Returns a table with a single record containing the number of records
distinct	Produces a table with the distinct combination of the provided columns of the input table
join	Merge the rows of two tables to form a new table by matching row for the same device
order by	Sort the rows of the input table into order by one or more columns
project	Select the columns to include, rename or drop, and insert new computed columns
take	Return up to the specified number of rows
top	Returns the first N records sorted by the specified columns
where	Filters a table to the subset of rows that satisfy a predicate

Scalar Operators

The following table summarizes operators:

Operators	Description	Example
==	Equal	1 == 1, 'aBc' == 'AbC'
!=	Not Equal	1 != 2, 'abc' != 'abcd'
<	Less	1 < 2, 'abc' < 'DEF'
>	Greater	2 > 1, 'xyz' > 'XYZ'
<=	Less or Equal	1 <= 2, 'abc' <= 'abc'
>=	Greater or Equal	2 >= 1, 'abc' >= 'ABC'
+	Add	2 + 1, now() + 1d
-	Subtract	2 - 1, now() - 1h
*	Multiply	2 * 2
/	Divide	2 / 1
%	Modulo	2 % 1
like	Left Hand Side (LHS) contains a match for Right Hand Side (RHS)	'abc' like '%B%'
!like	LHS doesn't contain a match for RHS	'abc' !like '_d_'
contains	RHS occurs as a subsequence of LHS	'abc' contains 'b'
!contains	RHS doesn't occur in LHS	'team' !contains 'i'
startswith	RHS is an initial subsequence of LHS	'team' startswith 'tea'
!startswith	RHS isn't an initial subsequence of LHS	'abc' !startswith 'bc'
endswith	RHS is a closing subsequence of LHS	'abc' endswith 'bc'
!endswith	RHS isn't a closing subsequence of LHS	'abc' !endswith 'a'
and	True if and only if RHS and LHS are true	(1 == 1) and (2 == 2)
or	True if and only if RHS or LHS is true	(1 == 1) or (1 == 2)

Aggregation functions

Aggregation functions can be used with the summarize table operator to calculated summarized values. Currently the following aggregation functions are supported:

Function	Description
avg()	Returns the average of the values across the group
count()	Returns a count of the records per summarization group
countif()	Returns a count of rows for which Predicate evaluates to true
dcount()	Returns the number of distinct values in the group
max()	Returns the maximum value across the group
maxif()	Starting in version 2107, you can use maxif with the summarize table operator. Returns the maximum value across the group for which Predicate evaluates to true.
min()	Returns the minimum value across the group
minif()	Starting in version 2107, you can use minif with the summarize table operator. Returns the minimum value across the group for which Predicate evaluates to true.
percentile()	Returns an estimate for the specified nearest-rank percentile of the population defined by Expr
sum()	Returns the sum of the values across the group
sumif()	Returns a sum of Expr for which Predicate evaluates to true

Scalar functions

Scalar functions can be used in expressions. Currently the following scalar functions are supported:

Function	Description
ago()	Subtracts the given timespan from the current UTC clock time
bin()	Rounds values down to a number of datetime multiple of a given bin size
case()	Evaluates a list of predicates and returns the first result expression whose predicate is satisfied
datetime_add()	Calculates a new datetime from a specified datepart multiplied by a specified amount, added to a specified datetime
datetime_diff()	Calculates the difference between two date time values
iif()	Evaluates the first argument and returns the value of either the second or third arguments depending on whether the predicate evaluated to true (second) or false (third)
indexof()	Function reports the zero-based index of the first occurrence of a specified string within input string
isnotnull()	Evaluates its sole argument and returns a Boolean value indicating if the argument evaluates to a non-null value
isnull()	Evaluates its sole argument and returns a Boolean value indicating if the argument evaluates to a null value
now()	Returns the current UTC clock time
strcat()	Concatenates between 1 and 64 arguments
strlen()	Returns the length, in characters, of the input string
substring()	Extracts a substring from a source string starting from some index to the end of the string
tostring()	Converts input to a string representation

Additional entities, operators, and functions for CMPivot from Configuration Manager

Important

These items aren't supported when you run CMPivot from Microsoft Intune admin center.

Type	Item	Description
Entity	AccountSID	Account SID
Entity	FileContent()	Content of a specific file
Entity	NAPClient	NAP Client
Entity	NAPSystemHealthAgent	NAP System Health Agent
Entity	RegistryKey()	Returns all registry keys matching the given expression (starting in version 2107)
Table operator	render	Renders results as graphical output

Next steps

To learn more about CMPivot, see Use CMPivot.