1806 has a LOT of features and while CMPivot is still early days there are some pretty cool things it can do especially for admins who maybe don’t fully understand how to use SQL or struggle creating complex WQL queries to get a list of machines. Now we can leverage CMPivot to query for information about devices and build the collection right from the parser. However it’s important to note that these collections are built using direct membership rules but that’s a separate topic and not what we’re here to talk about.
If you haven’t read how to configure access to CMPivot for anyone but an admin or you haven’t even touched CMPivot yet I suggest reading my post from yesterday regarding this
Before we can create a collection based on a CMPivot query we need to create a CMPivot query useful and actually does something for us so lets start with something practical something like find me all servers where a specific service is in a stopped state it might look something like this.
Service | where ((Name == 'WSUSService') and (State == 'Stopped'))
Now hopefully this doesn’t return any results in your environment but for my lab I’ve gone ahead and stopped the WsusService on my ConfigMgr server
Another example might be to find all servers missing the Petya vaccine file, the case last year where machines could be prevented from encrypting with Petya if a specific file existed in the root of C:\ we could look for something like that as well using the File() command.
Now we’ve found our server that has the WsusService stopped from here we could take two actions, we can take two actions if we happened to have a script stored that would restart a service remotely (hint hint) we could right click the server or we could shift click and multi-select the servers right click one of the selected objects and choose ‘run scripts’. Something important to note the script will ONLY run against the selected objects however, if you check the SQL db logs, or if you look at ‘Script Status monitoring’ it will look like it ran against the whole collection, don’t panic it didn’t.
And if you check in ConfigMgr Monitoring under script status
If you dig in though you’ll see that the script only returned a result for one client this is important and very different from other technologies in the industry that do this as ConfigMgr allows you the control to only query or interact with the machines you’ve chosen and doesn’t perform any client side filtering of the event.
Now, lets say you don’t have a script but instead you want to create a collection of machines based on this information you could instead select the machines you wanted to go to the collection or if you wanted all of them to go into a collection select nothing and click the ‘Add to Collection’ button in the top right corner.
Some things to note when you click this button it will open the ‘Create new Collection’ wizard. By default the limiting collection will be the collection you launched CMPivot from. Second, while my lab is relatively small with only 3 – 5 machines depending on the day the default behavior seems to be to create the collection using direct rules. In general I frown upon direct membership collections because of how quickly they can ‘fizzle’ if something goes wrong with the installed client but its a start maybe someday we will see these the Azure Analytics query get transformed into the WQL query that matches it.
I hope this was helpful and from this you can start thinking of ways you can use this to be more agile and responsive admins within your environment! Happy pivoting!
Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan currently works in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.