Unleashing the Power of CMPivot: Finding Installed Software Made Easy

I’m cleaning up blogs that I have started but never finished like Simple Steps to Configure BitLocker on Your Server. Anyways, I was creating a different blog about collections and I started to talk about CMPivot based collections. To finish the other blog, I needed to create this one first. This blog will show you How to use CMPivot to find Installed Software. We will cover using CMPivot to find Installed Software on a collection and a device. Finally, we will create a collection and export the results.

Gotcha

Don’t get me wrong I like CMPivot but it does have a gotcha. For many of the queries it will only tell you about those devices that are online. This means that the results are not necessarily 100% correct for your environment, particularly if you have other locations in different parts of the world.

There is a second gotcha about how much bandwidth it uses. But I’m not sure how many people care about that, but it should be noted. Particular for those with limited bandwidth sites or with Lots of devices.

There is one more gotcha. KQL is case sensitive. Being “lazy” this one catches me all the time! I will show this within the video.

Starting CMPivot

There are many different places to launch CM Pivot within Configuration Manager console (SCCM / ConfigMgr). Each one will be limited to a device or a collection. It will depend on where you launch it from.

For a Device you will find it within the ribbon bar or a right-click menu.

For a collection, again you will find it within the ribbon bar or a right-click menu.

See the video for more details.

CMPivot to find Installed Software

Below we will explore using CMPivot to return install software in various ways. Although we are only looking at installed software. All of what I show you also applies to the other CMPivot command.

Collection

To find all software on all devices you can use the below to get a list. Notice within the title bar that the collection name is listed. This is really the only way to determine how the results are scoped, e.g., to a collection or a device.

InstalledSoftware

Here is where it will get confusing, using the same command above when targeting a device in CMPivot. And you will see only the software for that device. See the video for the demo.

InstalledSoftware | summarize dcount(Device) by ProductName

Did you know that CMPivot will create tables? If you want to summarize the data by ProductName use the following query. You can then drill down on the Count to see which device have the software installed.

InstalledSoftware | summarize count() by ProductName | render piechart

InstalledSoftware | where (Publisher == 'Microsoft Corporation’) | summarize count() by ProductName | render piechart

InstalledSoftware | where (Publisher == 'Microsoft Corporation') and (ProductVersion > '14.28') | summarize count() by ProductName | render piechart

You will see me used these commands within the video but in a nutshell they will each create a piechart with various filter options.

Limit to a Software Title

InstalledSoftware | where ProductName == 'Microsoft PowerBI Desktop (x64)'

This command will allow you to find all devices with a particular software title installed. In order to not fill this blog with too many screenshots, you can see this within the video.

Device

InstalledSoftware | where ProductName == 'Microsoft PowerBI Desktop (x64)' and Device == 'Len02'

You can take it on step farther on and limit your results to a particularly device when you are within CMPivot targeting a collection.

InstalledSoftware

But what does it look like when you target and device? And again, let’s show all software for a single device. Notice that the title bar says it is limited to a device call len02.

Create Collection

By clicking the Create Collection button (in the top right), you can create a collection based on the results. Then follow the normal collection wizard prompts.

You can see from the wizard that all the collection is a list of direct memberships based on the results of query. Hence why the results will never update when new devices have the software installed.

Export

You can use the export feature to export the results as a csv.

From the menu in the top right, select Export. Then select Results to a file. In the Save window save the csv. After that you can do what ever you want with the results.

CMPivot to find Installed Software Video

In the video you will see everything here and much more.

If you have any questions about Unleashing the Power of CMPivot: Finding Installed Software Made Easy. Please feel free to contact me @GarthMJ Please also subscribe to my YouTube channel and newsletter.