A use case for CMPivot: look for installed software FAST!

This is an extensive explanation on how CMPivot can help you pull information from clients in Configuration Manager. Specifically, we’ll lookup an MSI Product Code for use with the Detection Method for an Application Deployment Type. But it covers some technical background and hints I hope you’ll find useful.

TL;DR If you already know about CMPivot and remote PowerShell and WMI, skip ahead to the CMPivot queries. If not, read on!

What is CMPivot?

If you’re on Configuration Manager CB 1806 or later, you have access to the CMPivot feature. If you’ve never used it, just select a Device Collection in the CM Console and click Start CMPivot in the Actions Ribbon. You can’t go wrong playing around with it as it only queries devices for stuff in a read-only fashion. But maybe start with a limited collection to avoid high resource impact.

When you open the CMPivot tool, the Welcome message has a good description of what it can be used for:

Note that the possibilities of CMPivot in CM 1806 were rather limited, as it was the first release of the feature. Still, it was good enough to be very efficient in countering a malware attack. And since 1810, we can query hardware inventory instances, including the custom one you defined in Client Settings.

See the full documentation on CMPivot here.

Live life in the fast lane

Because CMPivot queries a device in real-time, it is much faster than Hardware Inventory, which by default only pulls data from your clients once every 7 days. Even if you change that to once daily, it’s still too slow to help you detect change in the system’s software repository after a software deployment. Also, Hardware Inventory will pull everything you tell it to, as configured in the Client Settings. All that data easy accumulates to multiple MegaBytes per device. Multiplied by the number of client devices, this has an impact on bandwidth and resource usage on your Site Server and Site database.

CMPivot will only query the portion of data you’re after, on the limited number of devices you select, and it won’t store it in the database. And when you query on hardware inventory, it will pull the latest data from the Site database and update it based on CMPivot query results. This makes CMPivot instant, lightweight and powerful!

Once you’ve got the results you’re after, you can use them as a dataset and export to CSV or copy to the clipboard for further manipulation. Or, you can create a collection from the resulting set of devices and perform whatever action is needed to rectify a situation or further monitor it.

But the best thing is, you can target a PowerShell script to those systems, directly from the CMPivot screen. Scripts are pushed and followed up on through the fast channel as well, so you can perform instant action on your instant results. You can only select the scripts you’ve already created and had approved in your CM site though. Would be cool to enter PS code and run it right from CMPivot… Which is a very bad idea, which we WON’T put up on uservoice!

Seriously. Let’s not do that. If you’re not sure why that’s a bad idea, contact us for an offline bashing discussion.

Application Detection Method

What follows is how can we put CMPivot to good use in the context of Application Deployment and specifically, Detection Methods.

I prefer to detect the presence of an application through the MSI product code and version property. This of course depends on the type of application. If you have the MSI in the source content, you can use the Detection Rule wizard to read its properties and -shazam- you’re done. However, if you have a setup.exe or similar file and you can’t extract its contents to retrieve a .msi file from it, things are a little more complex. Sure, you can use file system or registry detection. Or use PowerShell – it can do anything! But I found a cool way to detect the MSI properties quick and easy through CMPivot.

First, I use a Windows machine to deploy and test packages and applications on. This can be a virtual machine which I can easily revert to a clean state, or a physical machine. Doesn’t matter in this context, as long as it has a CM client. Minimum client version must be 1806 to make use of CMPivot. It needs to be on 1810 or later to query hardware inventory!

Second, we need an application… I’ll be deploying the Sophos Endpoint. It uses the type of installer as described above and when I inspect its contents through 7-Zip, I see nothing but meaningless files. At least, to me they’re meaningless.

I always check the available switches or parameters of an installer. Sometimes there’s an administrative install method or an extract option. Not in this case. And no other switch that might help me to detect the application.

In cases like these, I either look at a machine that already has the application, or I install it on my packaging machine. I then look at either the Windows registry or the Add/Remove Programs inventory to get the MSI code, if any. That’s a manual chore. Which I despise. So I revert to PowerShell and I perform a remote query on the client. Doing this from my workstation, allows me to easily copy/paste the retrieved information into the CM Console.

The WMI class that is often queried for this purpose, is Win32_Product.

Bad practice alert!

A query to the Win32_Product class triggers Windows Installer to check for inconsistencies and triggers repairs. This is described in the MSDN documentation on Win32_Product and is mentioned all over the interwebz.

Now, since we have the CM Client installed, we have a much better WMI Class (or CIM instance) to query for installed software:
root\cimv2\sms\SMS_InstalledSoftware

It’s quicker to query and it doesn’t trigger any of the naughty side-effects of Win32_Product. So now we can use PowerShell or WMI Explorer or your weapon of choice to query for software remotely. This does, however, require WinRM to be configured to accept connections. Firewalls may get in the way as well.

CMPivot just works

It uses the fast communication channel to trigger the CM Client from the Site. It then reports back through state messages or, if you’re on CM 1810 and output is less than 80 KB, back through the fast channel.

So back to the case at hand. I’m at a customer with a rather strict security implementation, which does not allow for remote WMI queries or remote PowerShell instructions. I can setup a remote PSSession however, and here’s how I query for any Sophos products installed:

For reference, this query took 436 milliseconds. Fast enough for my standards. But it did take me a few minutes to get the output I was looking for. And I like to think I’m experienced on the matter.

All the same, I can now copy/paste the MSI code for the Sophos Endpoint from the SoftwareCode column, into the Detection Rule wizard. I also like to put a rule on the Product Version to be greater than or equal to a specific version, so I also copy/paste the ProductVersion value. And this is what my Application Detection Rule now looks like:

Splendid, that’ll do the trick!

So for remote PowerShell or WMI queries to work, there’s a few dependencies. And I need to know how to format output in PowerShell to get a usable result.

Now with CMPivot, our job got a lot easier. All you need is the CM client and at least PowerShell 4 on the target systems.
Again, the docs have all the details if you’re ready to learn more.

CMPivot queries

Here’s how easy it is.

Note: in my example, only 1 device reports back. If you select a collection with many online devices, you will get back as many results. Take this into account for resource usage. On the other hand, getting more results than what you strictly need, might actually help you to find the right information as you can compare results from multiple devices.

Steps

• Select a Device Collection where the test client is a member
• Start CMPivot from the Actions Ribbon or the right-click context menu
• Right-click the InstalledSoftware entity and click Insert

1. Query all software and right-click to filter on Publisher

2. Type query to get results directly

• Copy/paste the SoftwareCode and ProductVersion values into the Detection Rule wizard

Hints when working with CMPivot queries

• Refer to the Welcome page and the product documentation for syntax and limitations
• The query pane uses IntelliSense: type a letter, use arrows to select and hit tab
• The wildcard character is ‘%’ not ‘*’
• Use the ‘back’ and ‘forward’ arrows to move through your query history
• Queries will timeout after an hour for devices that don’t report back

That’s it. No more waiting for Hardware Inventory to be reported back. No firewalls getting in the way. Speed and power!

If only election results and tax refunds came back this fast…

I had fun creating this blog post and I hope you found it useful!