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%’)
- 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.
| 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.
| 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.
| 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.
| where ProductName !like ‘%Microsoft%’
| summarize dcount(Device) by ProductName