Category Archives: SCCM CMPivot

SCCM CMPivot

CMPivot Standalone Installer

SCCM 1906 introduced some cool stuff relating to CMPivot and one of my favourites is the standalone installer for CMPivot so you can use without having to access the SCCM admin console (the installer is available in the Tools folder under your main install folder e.g. C:\Program Files\Microsoft Configuration Manager\tools.

Tip – there is a dropdown box in the top left of the CMPivot window that allows you to browse and select the collection to work CMPivot against.

 

 

SCCM CMPivot

CMPivot Operators

== Equals
!= Not equals
like LHS contains a match for RHS
!like LHS doesn’t contain a match for RHS
contains RHS occurs as a subsequence of LHS
!contains RHS doesn’t occur in LHS
startswith RHS is an initial subsequence of LHS
!startswith RHS isn’t an initial subsequence of LHS
endswith RHS is a closing subsequence of LHS
!endswith RHS isn’t a closing subsequence of LHS

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

SCCM, SCCM CMPivot

CMPivot Examples

To accesss CMPivot, highlight a Collection containing the machines you wish to query against and then click Start CMPivot from the ribbon or right-click context menu.

The CMPivot query below returns all VMware devices in the collection used to run CMPivot against:

device | where ManuFacturer == ‘VMware, Inc.’ and (Device like ‘%CM%’ and Model like ‘%VM%’)

Notes

  • where and like are case sensitve
  • Device, manuFacturer, ‘VMware, Inc.’,, ‘%CM%’, ‘%svR%’ and ‘%VM%’ are not case sensitive.
  • Need to use a double == for equals.
  • Use % as a wildcard.
  • Text in single snicketts.
  • Use of () is for nesting but not required if nesting not used.

Count the number if devices per manufacturer.
Device | summarize dcount (Device) by Manufacturer

Below shown to indicate presentation – does not need to be all one line.
Device
| where (Manufacturer == ‘VMware, Inc.’)
| where (Device == ‘CM01’)

  • AD Security Groups that are members of local admins groups.

Administrators | where (PrincipalSource == ‘ActiveDirectory’ and ObjectClass == ‘User’)

  • Collection members where the SMS Executive service is running.

Service | where Name == ‘SMS_EXECUTIVE’ | where (State == ‘Running’)

  • Count the different OS version of the devices in the colllection and then display (render) as a bar chart.

OperatingSystem | summarize dcount (Device) by Caption | render barchart

  • Disk space with limited result set using the project operator to define the displayed columns.

LogicalDisk
| where (Description == ‘Local Fixed Disk’)
| project DeviceID, FreeSpace
| order by FreeSpace desc
| where (DeviceID == ‘C:’)

  • Installed software on all devices.  In the resultset note you can click on the underlined number to drill down.

InstalledSoftware | summarize dcount(Device) by ProductName

  • More specific – specifc application on a device.

InstalledSoftware
| where (ProductName == ‘Microsoft Monitoring Agent’)
| where (Device == ‘OM01’)

  • Count all installed software that does not have Microsoft in the product name using !like as the not like operator.

InstalledSoftware 
| where ProductName !like ‘%Microsoft%’
| summarize dcount(Device) by ProductName