BritV8

CMPivot is a tool in MEMCM (SCCM) that is under-rated or misunderstood by people. Its power is amazing, due to the fact it can live query machines in a collection, that are online, but also where devices are offline, use last known information from the MEMCM database

It uses the Kusto Query Language, so if you are familiar with basic SQL queries you can use this cheatsheet here https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet

If you are familiar with PowerShell then you recognise the Pipe operator | taking the output from one “cmdlet” and piping it to the next “cmdlet”

The following example shows how you can use two queries and join them and then only select the columns you want. So were are getting the Firmware information from each device where secureboot is false

then joining that information to the computersystem information

Then we are projecting the resultant object to only deliver 5 columns -device,Manufacturer,Model,Secureboot,UEFI.

Project is similar to SELECT in SQL or Select-Object in PowerSshell

Firmware | where SecureBoot == false | join (computersystem )| project device,Manufacturer,Model,Secureboot,UEFI

Here are some links on how to use the admin service to code these queries using powershell.

You can call CMPivot from the ConfigMgr Admin Service.

https://www.asquaredozen.com/2019/02/12/the-system-center-configuration-manager-adminservice-guide/

Here’s a post that goes into how you can query CMPivot over CMG. https://www.asquaredozen.com/2019/06/18/configmgr-1906-technical-preview-testing-cmpivot-over-cmg-using-adminservice/

Adam has a bunch of (somewhat working) scripts that you can see examples of usage for AdminService

https://github.com/AdamGrossTX/PowershellScripts/tree/master/ConfigMgr/AdminService