Create Custom Reports with Labels of Asset Intelligence

Usually, we use information from Add Remove Programs when it’s time to make software reports. For technical reasons, it’s not always easy to have adequate results. The use of custom labels in Asset Intelligence helps standardize and gather software information.

In this post, we will explain how to use the data from custom labels in asset intelligence to create custom reports. Before starting the reports, make sure the custom labels are set in your environment from how to maximize the use of custom views in Asset Intelligence.

Configuration Manager 2012 came with a couple of Asset Intelligence reports but nothing special based on the custom labels. The only useful report is Software 12A | Software titles with a specific custom label defined.

After this post, you will be able to do a report like the one below.

Views

First, here’s the list of SQL views used by SCCM for custom labels in Asset Intelligence :

If you want to see what they are made and also what are the relationships between them, right-click on each view in SQL Server Management Studio and select the top 1000 rows. You can access the database from the central site or primary.

SQL Server Management Studio is an essential tool to develop queries for reports in SCCM. Make sure your account has enough access to read the database.

For our report, we will use v_LU_SoftwareList_Editable because it contains everything we need.

Query

The report will show the total count by custom labels. Open SQL Management Studio and execute the below SQL query in your environment. You should see something like the below screenshot.

[pastacode lang=”sql” message=”” highlight=”” provider=”manual” manual=”SELECT%20DISTINCT%20CategoryName%20AS%20’Publisher’%2C%20FamilyName%20AS%20’Family’%2C%20Tag1Name%20AS%20’Type’%2C%20Tag2Name%20AS%20’Version’%2C%20SUM(Count)%20AS%20Total%0AFROM%20v_LU_SoftwareList_Editable%20SL%0AWHERE%20Tag3ID%20IS%20NOT%20NULL%0AGROUP%20BY%20CategoryName%2C%20FamilyName%2C%20Tag1Name%2C%20Tag2Name%2C%20Tag3Name%0AORDER%20BY%20Total%20DESC%0A”/]

We are now ready to create the custom report!

SCCM 2012 Custom Reports

To create a custom report, please follow those steps :

• Open the SCCM console and go to Monitoring / Overview / Reporting
• Right-click on Reports and select Create Reports

• On the Create Report Wizard, select type SQL-based Report
• Enter the name of the report Asset Intelligence – Inventoried Software with Custom Labels
• Fill the report description with This report shows inventoried software where a custom label is configured
• Click on Browse and select the folder of the report which is the report location. In this case, we use the Asset Intelligence folder
• Click Next

• Confirm details summary and click Next

• If the Create Report Wizard succeeds, you should have a green check everywhere.

• By clicking Close, Report Builder will automatically run

• Click Run

Here we are, ready to create the report.

Report Builder

There’s a lot of features that you can use in Report Builder to develop nice reports. This post will explain only the minimum requirement of creating a report.

We will not use Built-in Fields, Parameters and Images in our case but they are very useful when you want to standardize your reports.

We will only cover Data Sources and Datasets since they are the minimum requirement of creating reports. A data source is the database configuration and a dataset is the query used for the report.

Data Sources

• Right-click on Data Sources in Report Data on the left panel and select Add Data Source
• From the General tab, enter the name of your data source
• Select Use a connection embedded in my report and select Microsoft SQL Server from the connection type
• In the connection string, enter this string syntax

Data Source=ServerNameWithCMDatabase;Initial Catalog=CMDatabase

** Make sure to enter your information instead of ServerNameWithCMDatabase and CMDatabase **

• Click Test Connection to test your configuration and Ok when you’re done

** Make sure that your account has access to the database. If not, set up another account via Credentials on the left panel. **

Datasets

• Right-click Datasets in the left panel and select Add Dataset

• From the Query tab, enter the dataset name in the Name textbox
• Select Use a dataset embedded in my report and select the data source you created in the previous step
• In the Query field, copy-paste the SQL query from the Query section at the beginning of the post
• Click Ok

You are finally ready for the most fun part, creating the visual side of the report.

Design

• Rename the title to Asset Intelligence – Inventoried Software with Custom Labels
• Right-click anywhere in the report, select Insert / Table
• An empty table will appear

The idea is to take the dataset fields to the table. There are 2 ways to import fields :

• Expand Datasets and drag-drop each field into the table

• Click directly the Data textbox in the table, the complete list will appear and select the respective field

This is what your table will look like.

We won’t cover it but by modifying text box properties, you will be able to change font colour and modify the style.

• To modify properties, right-click the text box and select Text Box Properties

Testing

When the design is finished, test the result in Report Builder before thinking to import the report into production.

• Click the Run button on the top left

Deploy

Happy with the results? Let’s deliver the report.

• Click the Save button on the top left.

Verification

By saving the report in Report Builder, your report will be updated in SSRS and your console. For sure, you need to install SCCM 2012 reporting services point to see it in SSRS.

SSRS

• Open your web browser, go to your reporting website (usually http://nameoftheserver/reports) and discover the report in the right location

Console

• From the console, click on Monitoring / Overview / Reporting / Reports and discover the report in the right location

This formula can be used for any situation. Only your imagination is the limit.

You are now able to create reports for installed software by standardizing the custom labels. Be sure to keep an eye on the custom labels and inventoried software in Asset Intelligence. Any new versions detected by the system won’t be added automatically.

Asset Intelligence Blog Series

• Part 1 | Why should you use Asset Intelligence in SCCM 2012
• Part 2 | How to maximize the use of Asset Intelligence with Labels
• Part 3 | Create Custom Reports with Labels of Asset Intelligence
• Part 4 | Merging Last Usage of Software Metering with Custom Labels of Asset Intelligence
• Part 5 | Automatically Uninstall Unused Applications based on Software Metering with SCCM

sccm 2012 custom reports