SCCM CMPivot – gather security and compliance information about clients in real-time.

Applies to:

SCCM Current Branch (CB) 1910

SCCM Current Branch (CB) 1906

SCCM Current Branch (CB) 1902

SCCM Current Branch (CB) 1810

SCCM Current Branch (CB) 1806

[What is CMPivot?]

CMPivot is a built-in tool to SCCM CB 1806 and newer.  It helps with using a subset Kusto query language (the same used in Azure Log Analytics queries) about security/compliance/device/user information in real-time.  It will let you pivot, filter, group and refine live data.

How is SCCM capable of getting this information real-time?

A:  Using SCCM’s “fast channel”. This communication channel from server to client is also used by other features such as client notification actions, client status, and Endpoint Protection.”

How can you use it in your environment that might already have an EDR such as WDATP?  It can go hand in hand.  SCCM CB CMPivot + MDATP better together.

Here is an example of a 3rd party Consulting firm that used CMPivot and MDATP to find EMOTET outbreaks:

Microsoft Defender ATP & Configmgr CMPivot with a CMG (Cloud Management Gateway) better together; How we saved the customer from Emotet related malware

[Getting started]

Prerequisites
https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#prerequisites

image

image

Start CMPivot
https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#start-cmpivot

SCCM CB 1806 CMPivot Entities (aka Schema) SCCM CB 1810/1902/1906/1910 CMPivot Entities (aka Schema)
n/a ActiveSyncService
Administrators Administrators
n/a AMTAgent
AppCrash AppCrash
n/a AppVClientApplication
n/a AppVClientPackage
AutoStartSoftware AutoStartSoftware
n/a BaseBoard
n/a Battery
Bios Bios
n/a BitLocker
n/a BootConfiguration
n/a BrowserHelperObject
CcmLog() CcmLog()
n/a CCMRAX
n/a CCMRecentlyUsedApplications
n/a CCMWebAppInstallInfo
n/a CDROM
n/a ClientEvents
n/a ComputerSystem
n/a ComputerSystemProduct
n/a ConnectedDevice
Connection Connection
n/a Desktop
n/a DesktopMonitor
Device Device
Disk Disk
n/a DMA
n/a DMAChannel
n/a DriverVxD
n/a EmbeddedDeviceInformation
n/a Environment
EventLog() EventLog()
File() File()
FileShare FileShare
n/a Firmware
n/a IDEController
n/a InstalledExecutable
InstalledSoftware InstalledSoftware
IPConfig IPConfig
n/a IRQTable
n/a Keyboard
n/a LoadOrderGroup
n/a LogicalDisk
n/a MDMDevDetail
n/a Memory
n/a Modem
n/a Motherboard
n/a NetworkAdapter
n/a NetworkAdapterConfiguration
n/a NetworkClient
n/a NetworkLoginProfile
n/a NTEventlogFile
n/a Office365ProPlusConfigurations
n/a OfficeAddin
n/a OfficeDocumentMetric
n/a OfficeVbaSummary
n/a OperatingSystem
n/a OperatingSystemRecoveryConfiguration
n/a OptionalFeature
OS OS
n/a PageFileSetting
n/a ParallelPort
n/a Partition
n/a PCMCIAController
n/a PhysicalDisk
n/a PhysicalMemory
n/a PNPDEVICEDRIVER
n/a PointingDevice
n/a PortableBattery
n/a Ports
n/a PowerCapabilities
n/a PowerClientOptOutSettings
n/a PowerConfigurations
n/a PowerManagementDaily
n/a PowerManagementInsomniaReasons
n/a PowerManagementMonthly
n/a PowerSettings
n/a PrinterConfiguration
n/a PrinterDevice
n/a PrintJobs
Process Process
n/a Processor
n/a ProcetedVolumeInformation
n/a Protocol
n/a QuickFixEngineering
Registry() Registry()
n/a SCSIController
n/a SerialPortConfiguration
n/a SerialPorts
n/a ServerFeature
Service Service
n/a Services
n/a Shares
SMBConfig SMBConfig
n/a SMSAdvancedClientPorts
n/a SMSAdvancedClientSSLConfigurations
n/a SMSAdvancedClientState
n/a SMSDefaultBrowser
n/a SMSSoftwareTag
n/a SMSWindows8Application
n/a SMSWindows8ApplicationUserInfo
n/a SoftwareShortcut
SoftwareUpdate SoftwareUpdate
n/a SoundDevices
n/a SWLicensingProduct
n/a SWLicensingService
n/a SystemAccount
n/a SystemBootData
n/a SystemBootSummary
n/a SystemConsoleUsage
n/a SystemConsoleUser
n/a SystemDevices
n/a SystemDrivers
n/a SystemEnclosure
n/a TapeDrive
n/a TimeZone
n/a TPM
n/a TPMStatus
n/a TSIssuedLicense
n/a TSLicenseKeyPack
n/a UninterruptiblePowerSupply
n/a USBController
n/a USBDevice
User User
n/a USMFolderRedirectionHealth
n/a USMUserProfile
n/a VideoController
n/a VirtualMachine
n/a VirtualMachine64
n/a Volume
n/a WindowsUpdate
n/a WindowsUpdateAgentVersion
n/a WriterFilterState

[Architecture]

image

Inside CMPivot (CMPivot Internals)
https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#inside-cmpivot

Infographic: Get real time information with CMPivot
https://gallery.technet.microsoft.com/Infographic-Get-real-time-d43a084e

[What about scalability and performance?]

Q:  What’s the impact on the network?

A:  Scalability for the biggest query (high water mark), 1K.
Let’s say that you have a SCCM collection that have 50,000 endpoints, that is ~50MB max over the network.

Q:  How about disk i/o performance hit?

A:  It doesn’t have a 2GB (1.5GB and a 0.5 GB) database on each Windows 10/8.1/7 and/or Windows Server 2019/2016/2012R2/2012/2008R2 based machines like some of the 3rd party competitors.  Thus, it will run just fine on SATA based drives.

[What’s new in CMPivot?]

You are able to tell that with the SCCM CB release schedule, that there has been great strides on making this component even better with your feedback(s).

What’s new in SCCM CB 1906 and CMPivot?

image

1) CMPivot is a standalone app. No more need to the SCCM console installed in order to access CMPivot.

· Reference:

CMPivot standalone

2) Add joins, additional operators, and aggregators in CMPivot

https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#bkmk_cmpivot_joins

“You can now use CMPivot from the CAS and edit and copy existing PowerShell scripts used with RunScripts.”

· Reference:

Update 1902 for Configuration Manager current branch is now available

“Updates to CMPivot for real-time queries: CMPivot provides a simple way to quickly investigate the whole device estate using pre-built queries, pivoting the data to answer specific questions relating to compliance and security, for example. You can now access CMPivot from the Configuration Manager Central Admin Site (CAS), enabling you to quickly run these queries and remediate where needed.”

“Run CMPivot from the central administration site – You can now run CMPivot from the central administration site.” (CAS)

“Improvements to CMPivot – CMPivot now allows you to save your favorite queries and create collections from the query summary tab. Over 100 new queryable entities added, including for extended hardware inventory properties. Additional improvements to performance.”

Extended CMPivot – You now have real-time access to extended inventory information for devices in your environment.

“This release gives you the ability to visualize your CMPivot results using the Azure Log Analytics render operator. You can now choose from the following types of visualizations: bar chart, column chart, pie chart and time chart. Visualizations help you analyze query results and discover key insights, so you can more quickly answer business questions, troubleshoot issues, and respond to security incidents.

CMPivot now also includes the ability to query and pivot on hardware inventory class information. This not only significantly enhances what can be queried out-of-the-box, but also enables a standardized way to extend CMPivot by adding new hardware inventory class definitions. Even better, CMPivot will immediately render data from the last hardware inventory scan while simultaneously pulling live data from online clients — giving you a view across online and offline devices!
Other improvements to CMPivot include support for scalar functions and scalar operators, and a query summary which displays the status of the query on the device where it was executed.“

With the 1806 update for Configuration Manager current branch, we continue to invest in providing cloud powered value to your existing Configuration Manager implementation with additional co-management workloads and simplified cloud services. We’re also very excited to announce a powerful new capability that we call CMPivot, building off our real-time script capability. CMPivot is a new in-console utility that provides access to real-time state of devices in your environment.

“You will also notice some continued user interface enhancements to the CMPivot feature.”

“Have you ever wanted to take real-time action to quickly respond to an event or discover a wide variety of information about your devices and hunt for anomalies? CMPivot is a new in-console utility that provides access to such real-time state of devices in your environment. It has the ability to immediately run a wide variety of queries on all currently connected devices in the target collection and return the results. You are then able to act upon those results. For example, in the scenario of mitigating speculative execution side channel vulnerabilities, one of the requirements is to update the system BIOS for your devices. You can use CMPivot to quickly query on system BIOS information and find clients that are not in compliance. You can then switch to Run Scripts to quickly remediate them with a scripted solution. ”

[Start using CMPivot]

How to use CMPivot
https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#how-to-use-cmpivot

Get started with Azure Monitor log queries
https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/get-started-queries?toc=%2Fazure%2Fazure-monitor%2Ftoc.json

Tip:  Like many other of our (Microsoft) products, it has Intellisense enabled.  In another words, if you press the “Tab” key in the keyboard, it provides you with options to use.

As of SCCM CB 1810, there are over 130 entities that you are able to query live.  Do you have suggestions on additional entities that would make your life easier?  Please add it to the comment in this blog post.

Depending on the amount of machines that are online, we might see a color of red, yellow (offline) and green (online).

[Examples]

Example: Get a list of the machines

Device | distinct Device

Example: Get a list of the machine, domain, and users

Device | distinct Device, Domain, UserName

ComputerSystem

| project Device, Manufacturer, Model

| join (OperatingSystem | project Device, OSVersion=Caption)

Example:

List 50 last lines of a specific SCCM log file on a specific computer

CcmLog(‘CCMLogName.log‘) | where (Device == ‘ R61145677‘) | order by DateTime desc | project Device, LogText, DateTime


Example :

OS | where Version like ‘10%’


Example :

TPM


Example : BIOS version (Is your firmware up to date to prevent Spectre/Meltdown?)

Bios | summarize dcount( Device ) by Version


Example : Check free disk space.

Disk | where (Description == ‘Local Fixed Disk’) | where isnotnull( FreeSpace ) | order by FreeSpace asc


Example :  You need to get the list of “Local Administrators” group members using SCCM, but with script and PowerShell disabled on the clients machine, is that possible in any other way?

A:  If you have CMPivot, the 4th class in CMPivot is Administrators. Which lets you use SCCM to query admin members for all online clients. 

And then you can even export that list to a csv file.

Or you could query all machines in that SCCM collection, where an admin has Local Administrator rights.

Administrators | where (Name == ‘DOMAIN\\USERNAME’)


Example : The Security Admin asks if a certain CVE (in a KB) is installed on the Windows 10/8.1/7 based machines?

QuickFixEngineering

Right-click, filter

Bing


Example : Tells you the current configuration of the “Windows Update service” and what the WSUS/MU (WU) server is supposed to be.

Windows Update


Example :

SoftwareUpdate


Example :

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)


Example :

AppCrash

AppCrash
| summarize countif( (Device == ‘R61145676’) ) by Device
| where (countif_ > 0)

Proactively resolve application failures


Example :

Eventlog(‘Security’)

Last 50 events from the Security event log

EventLog(‘Security’) | order by DateTime desc

Last 50 events from the Security event log from a specific computer

EventLog(‘Security’) | where (Device == ‘DeviceName’) | order by DateTime desc


Example:

List a specific process

Process | where (Name == ‘ProcessName.exe’)


Example :

Service

Service | summarize dcount( Device ) by Name

Service | where (Name == ‘Browser’) | summarize count() by Device

Stop a running service


Example :  Build a Collection with CMPivot for machines that do not have Application “Identity” service installed

A: 

Service | summarize countif( (Name == ‘AppIDSvc’) ) by Device | where (countif_ < 1)


Example :

File(“C:\\DEMO\\badfile.exe’)

| summarize count() by Version,Hash

| order by count_

TIP:

%windir% e.g. does work but not %appdata% or %localappdata%


Example :  Looking for Files in CMPivot:

A:  File(‘C:\\windows\\system32\\*’) | summarize countif ((FileName ==’C:\\windows\\system32\\scvhost.exe’)) by Device | where (countif_<1)

Note:  SvcHost.exe is the actual Microsoft process that hosts the services, ScvHost.exe in the other hand, that was flagged as fishy.

Or

Looking for a file called Outlook.exe, it would then be:

File(‘c:\\Program Files\Microsoft Office\Office16\OUTLOOK.EXE\\*’) | summarize countif ((FileName ==’c:\\Program Files\Microsoft Office\Office16\\OUTLOOK.EXE’) by Device | where (countif_<1)

File(‘C:\Users\*\AppData\Local\Microsoft\Outlook\Offline Address Books\*\ubrowse.oab’)


Example :

Registry(‘hklm:\\Software\\Demo’)


Example :

CCMRecentlyUsedApplications

| summarize dcount( device ) by ProductName

| top 0 by dcount_

| render columnchart


Example : List all Auto Start Software on a specific device

AutoStartSoftware | where (Device == ‘xx’)

CCMRecentlyUsedApplications

| summarize dcount( device ) by ProductName

| render columnchart

// We could change “columnchart” to “piechart”

// or barchart, or timechart

CCMRecentlyUsedApplications

| summarize dcount( device ) by ProductName

| top 0 by dcount_

| render columnchart


Example: Demo “stopped” services.

Service

Under “State”, for example, select “Stopped”

Right click on “Stopped” and click on “Filter by”

which then will add

Service

| where (State == ‘Stopped’)

Right click on “Stopped” and click on “Show Devices With”

Service

| summarize countif( (State == ‘Stopped’) ) by Device

| where (countif_ > 0)


Example: Active file share information excluding Administrative Shares (Share$)

FileShare | where (Type == 0)


Example: Count all device with SMB1 enabled

SMBConfig | summarize countif( (EnableSMB1Protocol == true) ) by Device | where (countif_ > 0)

Try it out and let us know what you think about it.

Thanks,

Yong

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: