CMPivot in ConfigMgr 1806 – Part 1 – What is CMPivot and your first query

Since the early days of ConfigMgr when it was first named ‘SMS’ there was always this background joke that what SMS really stood for was ‘Slow Moving Software’ however with the advent of CMPivot we can begin to leave that behind us and remove the stigma of slow. In recent months there has been this new buzzword around the office and that word is ‘agile’ and CMPivot is the first thing in ConfigMgr that really jumps on the bandwagon of get me current, accurate data about my environment and get it to me right now. Historically we’ve had to wait for software inventory or hardware inventory to complete in order to get new chunks of data. Now there is a lot of information we can get really quickly using CMPivot and we don’t have to create custom WMI information all we have to do is wait for CMPivot to return real time information back from our clients.

The only downside at this point is we can only use specific queries are available within CMPivot. However, the upside is that I’m sure that between the new community center feature and user voice we can get new CMPivot queries to fill in the spaces we want. This will be a series of posts about CMPivot and some of the cool new things you can do with it!

Part 1 – What is CMPivot and your first Query

Part 2 – Create a Collection from query information

Part 3 – CMPivot and SQL Reports

What is CMPivot

CMPivot is a new feature in ConfigMgr 1806 that allows ConfigMgr administrators and others the ability to gather information about clients in real time. The tool uses the same query language that Azure analytics does and the same language structure. When the ConfigMgr client is installed a script is added to the C:\Windows\CCM\ScriptStore folder that contains… well it contains a lot of things however if you think you’ll be able to just go crack that bad boy open and read it you might want to think again. Because we ONLY ever want the script to read information and send it back to ConfigMgr the script is locked down to only allow the ‘system’ account to run it, in fact I have a feeling that messing about with it is probably unsupported. However if you mess around with it you can seize ownership of the script and crack open it’s contents to see some of the cool stuff the script does. My Personal favorite bit that I pulled out was:

$Session =  [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$null))
$Searcher = $Session.CreateUpdateSearcher()
# Search for any uninstalled updates
$MissingUpdates = $Searcher.Search("DeploymentAction=* and IsInstalled=0 and Type='Software'")

A nice little bit that pulls out information on what updates are missing from a device! However this also highlights something important the way this investigates updates doesn’t take into account updates you may not want to install on machines so always validate what data is actually being returned and what the criteria in use is.

How to Use CMPivot

So we’ve talked about what CMPivot is which but how do we actually use it! First lets take a look at the RBAC around it. Unfortunately at this point the only people who will be able to really leverage CMPivot are people with the run-scripts permissions and by default the only people who get that are full administrators in ConfigMgr. If you’re interested in enabling the permission you’ll need to give a user:

  • SMS_Scripts – Read “Yes”
  • Collections – Run Script “Yes”

If the user doesn’t have the SMS_Scripts – Read permission they won’t even see the CMPivot option in the console and if they have the SMS_Scripts Read Permission but not the ‘Collections run Scripts’ permission the will be able to see the option but it will be greyed out.

OK With permissions out of the way we can go ahead and get started by right clicking the collection we want to query and select the ‘Start CMPivot’ option – or you can simply click the ‘Start CMPivot’ option in the ribbon at the top.

Once you select the CMPivot option it will open up the CMPivot interface and you can begin to plan out your query currently the entities that you have to chose from are on the left hand side and you can use the Azure Log analytics data flow model language to figure out how to build expressions for more information you can read up on it here:

Lets start with something simple right at the top of the list, local administrators this has always been a challenging item and there are lots of ways to solve it the most common in the past being to use ConfigMgr baselines and WMI to inventory the information now though we can get this information and export it into an excel spreadsheet to take straight to management! We have two options to start we can either click the ‘Query’ tab along the top, or we can double click the entity we want to query in this case ‘Administrators’.

When you go to double click ‘Administrators’ take a moment to hit the check box and examine the properties that are returned they are:

  • Device
    • The name of the device you are getting information about
  • Object Class
    • Is it a Group that is in the Administrators group, or a user type object?
  • Name
    • What is the name of the group or user this always includes the full name that means ‘computername\localuser’ or ‘\username’
  • PrincipalSource
    • What is the source location of the security principal object, Active Directory, Local or something else.

Now if you did a double click on ‘Administrators’ and tried to run it you might be very disappointed. The default behavior of a double click for CMPivot is to expect to pass the entity through to perform some type of action like a count. We’ll cover that and escape characters next. Suffice to say if you remove the “|” I displayed in the image above you’ll end up with something like this as a response:

Pretty cool right? Now my lab is small so it doesn’t take long for me to run but you’ll notice that I’ve got three different error codes, I’m going to go into finding out what they mean using SQL in the next post. The above information while useful isn’t all that great for us and in a large environment it might even be totally overwhelming, so lets use a little language to see if we can make it better. Now the first thing to notice, if you run a query, and then you EDIT the query with a filter or other action to the original statement it does not re-query the members of the collection or update the information it simply filters around the data. So lets start with something that might be common maybe we want to find every local administrator account that isn’t the default administrator account.

Now, we know that PrincipalSource will tell us if a user account is local or a member of Active Directory so we can start there

Administrators | where (PrincipalSource == 'Local')

This will get us all members  of the ‘Administrators’ group that are local to the machine and not domain accounts. However from the testing I’ve done there doesn’t seem to be a good way to only return records that are not like Administrator. I’m going to do some more testing on this and if I find a way or if I find out it’s a bug I’ll report back!

*UPDATE* – I’ve since learned it was in fact a bug and the ability to include a ‘Not like’ should be fixed in the next version of the parser!

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.


  • Thanks for the article. We think we have discovered another consideration when trying to grant access to CMPivot to RBAC users. It appears that in addition to the script related permissions listed here, an RBAC user with those permissions will also require that the Default security scope is added to the role with those permissions. Withouth this scope, starting CMPivot returns a message that it cannot be used due to an incompatible script version. I have not found anywhere in the console where custom security scopes can be applied for CMPivot.

    • That’s a good thing to note about scopes they can really add complexity depending on how they are used.

    • Further threading the needle on this, it looks like you only need the Read on SMS_Scripts for the default scope, but not Run Scripts on a collection (which makes sense when you really think about it, a collection doesn’t have a “Scope”).

      My guess is CMPivot is implemented as a “hidden” script in the default Scope, so you need that perm to read it. But to run it on a collection the user will also need to have access to run scripts against that collection.


Categories use cookies to ensure that we give you the best experience on our website.