Installed Software Reporting with SCCM

Ioan Popovici

Published in

MEM.Zone

4 min readDec 6, 2018

Installed software reporting made easy…

🚨🚨 IMPORTANT NOTICE🚨🚨

🦄 We moved to our brand new blog at MEM.Zone 🦄

🚨🚨 IMPORTANT NOTICE🚨🚨

Report release history

I was sitting on this one for a while for no good reason. I built it and then completely forgot about it. A few days ago I was trying to find a part of the query, used in this report realized that I forgot to publish it.

Most software reports out there are pretty basic so I decided to buid something new. The idea was to allow filtering by multiple software names and SQL wildcards. This principle applies to both inclusions and exclusions. The only drawback is that you have to use a SQL string parser for splitting the CSV strings. There is no need to re-invent the wheel, you can use the one created by Michelle Ufford.

At first, I built two reports, one displaying the data by device name and the other by publisher. In the end I ended up by merging them since it does not make sense to have to maintain two reports using the same query.

Also I’ve updated my report template since the old one was an over-designed pile of c**p, too hard to maintain and to reuse. My other reports will also get the upgrade in time.

Notes
The reports have two data sources since my CM DB in on a HA cluster. I need access to the ReportServer DB to get the report description in the ReportDescription dataset. Feel free to switch to one dataset.

Import the SSRS Report

Download the report file

## Installed Software Report
SW Installed Software by User Selection.rdl

Internet Explorer on and navigate to http://YOUR_REPORT_SERVER_FQDN/Reports
Choose a path and upload the previously downloaded report files.
Replace the DataSource in the reports. You can use just one datasource if your CM and Reporting DBs are on the same server.

Install String Parser

You can create a new database to host the support function or just add it to the CM database. Fair warning, this counts as ‘modifying the CM database’ to Microsoft and they might deny support because of it. The right way to do this is to create a separate database for this purpose.

Create the ‘CM_Tools’ database

Create the ‘CM_Tools’ database by executing the following code in your SQL Management Studio:

/* Create support function database */
CREATE DATABASE CM_TOOLS

If you change the ‘CM_Tools’ name to something else you will need to modify the hardcoded function calls in the SoftwareData dataset report query:

/* Populate SoftwareLike table */
...
FROM CM_Tools.dbo.ufn_csv_String_Parser(@SoftwareNameLike, ',');
.../* Populate SoftwareNotLike table */
...
FROM CM_Tools.dbo.ufn_csv_String_Parser(@SoftwareNameNotLike, ',');
...