SCCM Current Branch 1806 is loaded with amazing features. In this post, we will cover the latest addition in the reporting space: CMPivot. SCCM has always been good with reporting and inventory of it’s managed devices but SCCM data is up-to-date at the last time the inventory has been run. SCCM CMPivot allows SCCM administrators to initiate a live query on selected computers on a specific topic. The result of that query can then be used to mitigate and fix potential issues.

How many time were you asked “what is the current state as of NOW?” well, you’ll finally be able to answer appropriately with SCCM CMPivot.

In this post, we will covert how to use CMPivot to create a basic query and how to use the result of this query.

SCCM CMPivot Prerequisites

Important Info

CMPivot can leverage the power of the Cloud Management Gateway. Doesn’t seems like any limitation are documented so far.


How to use SCCM CMPivot

The first step is to select a computer collection to run CMPivot against those computers. We will select our All Server Clients collection. In our example, we will query the computer to have information about their Operating System version.

  • Select the collection and from the Ribbon, select Start CMPivot


  • The CMPivot window show up to configure the query


  • The Home tab provides details on how to create queries
  • Select the Query tab



Only one CMPivot targeting a collection can be executed at a time by the SCCM console.

  • Select one of the available entities to target, Right click on it and select Insert


  • The entities to query on is then added to the query


  • Running the query as is will return all information about that entities



Run query will take time. Remember this is a live query against online computers, including computers connected to the Cloud Management Gateway

  • To refine the query, add the pipe character |, this will provide the list of operators to be added after the |


Important Info

From Microsoft : ” CMPivot uses a subset of the Azure Log Analytics data flow model for the tabular expression statement. The typical structure of a tabular expression statement is a composition of client entities and tabular data operators (such as filters and projections). The composition is represented by the pipe character (|), giving the statement a very regular form that visually represents the flow of tabular data from left to right. Each operator accepts a tabular data set “from the pipe”, and additional inputs (including other tabular data sets) from the body of the operator, then emits a tabular data set to the next operator that follows: entity | operator1 | operator2 | …”

  • Then CMPivot will propose entities


  • Here we addVersion to be like ’10.%’


  • CMPivot will automatically correct it to look that way, once we Run the query.


  • This provides a subset of computers


  • Now that we have a list of computer, the following actions can be performed :
    • Create a collection based on those computers
    • Pivot to, which allows seeing other entities in CMPivot for a specific computer
    • Run Script, this can be run on multiple computers at a time
    • Remote Control a specific computer
    • Open the Resource Explorer for a specific computer


  • The Pivot to is a quick access to other Entities for a specific computer.


This feature is brand new and we have yet to found a real-world scenario that requires this. We’ll update this post when we have some interesting queries.

Do you have a great query that could help other SCCM administrators out there? Share it in the comments below!

More details and examples available on Microsoft Docs


Comments (8)


12.18.2019 AT 03:28 PM
The weak comforted themselves with tears, and the strong honed themselves with sweat


08.29.2019 AT 03:26 PM
What about if your policy blocks all but signed scripts?


08.28.2019 AT 10:16 AM
Running 1902 in class, no CMPivot button shows.

Tan Tran

05.14.2019 AT 02:15 PM
We use the CMPivot to query EventID 6005 for startup status during Cluster Maintenance Reoot.


05.14.2019 AT 03:47 AM
I'm running a CMPivot query (TPM) for a collection of 365 objects, but the query only returns findings on 115 objects. Why is this? I'm trying to identify each objects TPM SpecVersion.

Matthew Notier

04.09.2019 AT 12:51 PM
Have you found any 'real world' application for this tool?

Sahil Sharma

10.02.2018 AT 01:04 AM
easy to understand, thanks..


08.06.2018 AT 09:07 AM
Can we have a query to check the client whose evaluation is failed