How to use SCCM CMPivot

Jonathan LefebvreSCCM2 Comments

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.

SCCM CMPivot

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

SCCM CMPivot

  • The CMPivot window show up to configure the query

SCCM CMPivot

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

SCCM CMPivot

Note
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

SCCM CMPivot

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

SCCM CMPivot

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

SCCM CMPivot

Remember
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 |

SCCM CMPivot

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

SCCM CMPivot

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

SCCM CMPivot

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

SCCM CMPivot

  • This provides a subset of computers

SCCM CMPivot

  • 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

SCCM CMPivot

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

SCCM CMPivot

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

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.43 out of 5)
Loading...

Share this Post

Contributor of System Center Dudes. Based in Montreal, Canada, Senior Microsoft SCCM consultant, working in the industry for more than 10 years. He developed a strong knowledge of SCCM and MDT to build automated OS deployment solution for clients, managed large and complexe environment, including Point of Sale (POS) related projects.

2 Comments on “How to use SCCM CMPivot”

Leave a Reply

Your email address will not be published. Required fields are marked *