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