Tag Archives: 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