Using SCCM CMPivot

With the release of SCCM current branch 1806 there is a lot of new features. One feature I really like is CMPivot as this gives real-time results from queries instead of doing SCCM SQL query which will only have information from the last inventory scan from the SCCM agent.  This is very helpful if you want to run real-time queries on devices to check for  services status, installed software, OS information and more.

There are some prerequisites for using CMPivot:

• SCCM must be up to 1806 or higher
• The Configuration Manager administrator needs the Read permission on the SMS Scripts object, and the Run Scripts permission on the Collection object. The Scripts Runner role has these permissions.
• SCCM clients must be running the 1806 agent or higher
• To gather data for the following entities, target clients require PowerShell version 5.0:
  • Administrators
  • Connection
  • IPConfig
  • SMBConfig

To launch CMPivot open the SCCM console and go to device collections. Select the collection you want to query.

Once you open CMPivot you will get the welcome screen this will give information on how to run queries and the different object and functions that can be queried. 

I will just be doing a basic query to check for a specific service. Highlight the entities you want to query and select insert.  If you run the queries with just service it will return all services from every device in the collection.

Use the where operator to filter results to only what is required.  Just put in a pipe | and add where then CMPivot will present the list of available entities.

Below I’m looking for all servers that have WinRM service this can be changed to any service that needs to be queried. 

We can also combine queries to get list of devices that have a service installed but the service is not running. This can be usefully if you need to troubleshoot an application issue on multiple devices that might require a certain services.  I used to use script to check this type issue but CMPivot is a lot easier and all the result are returned directly back to the CMPivot console. 

Once the query is completed there are some other options that can be run.

• Create a collection based on the devices returned from the query 
• Pivot to: This can be used to look up other info on a selected device
• Remote Control for the selected device
• Open the Resource Explorer for the selected device
• Export list of devices to a CSV or clipboard 

Run script is the only task that can be run on single or multiple devices.

Another use for CMPivot is querying a specific file. A version number can added to the query. A good use case for this is if there is an application upgrade and the version number of the file changes this can be used to find devices that have not been upgraded successfully.