Create Custom Reports with Labels of Asset Intelligence

Nicolas PilonAsset Intelligence, SCCM, SSRS5 Comments

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 a 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 one useful is Software 12A | Software titles with a specific custom label defined.

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

sccm 2012 custom reports

Views

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

sccm 2012 custom reports

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 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 have 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.

SELECT DISTINCT CategoryName AS 'Publisher', FamilyName AS 'Family', Tag1Name AS 'Type', Tag2Name AS 'Version', SUM(Count) AS Total
FROM v_LU_SoftwareList_Editable SL
WHERE Tag3ID IS NOT NULL
GROUP BY CategoryName, FamilyName, Tag1Name, Tag2Name, Tag3Name
ORDER BY Total DESC

sccm 2012 custom reports

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

sccm 2012 custom 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 Asset Intelligence folder
  • Click Next

sccm 2012 custom reports

  • Confirm details summary and click Next

sccm 2012 custom reports

  • If the Create Report Wizard succeed, you should have green check everywhere.
  • By clicking Close, Report Builder will automatically run

sccm 2012 custom reports

  • Click Run

sccm 2012 custom reports

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 minimum requirement of creating report. A data source is the database configuration and dataset is the query used for the report.

sccm 2012 custom reports

Data Sources

  • Right click on Data Sources in Report Data on left panel and select Add Data Source
  • From General tab, enter the name of your data source
  • Select Use a connection embedded in my report and select Microsoft SQL Server from 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 **

sccm 2012 custom reports

  • 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

sccm 2012 custom reports

  • From 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 previous step
  • In the Query field, copy paste the SQL query from the Query section at the beginning of the post
  • Click Ok

sccm 2012 custom reports

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

sccm 2012 custom reports

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

  • Expand Datasets and drag drop each field into the table

sccm 2012 custom reports

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

This is what your table will look like.

sccm 2012 custom reports

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

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

sccm 2012 custom reports

Testing

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

  • Click Run button on the top left

sccm 2012 custom reports

sccm 2012 custom reports

Deploy

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

  • Click 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

sccm 2012 custom reports

Console

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

sccm 2012 custom reports

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

You are now able to create report 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

sccm 2012 custom reports

Founder of System Center Dudes. Nick has been awarded in 2016 as a Microsoft MVP in Enterprise Mobility category.
Working as a senior SCCM and Intune advisor as well as a specialist Microsoft Cloud solutions specialist.
Create Custom Reports with Labels of Asset Intelligence
5 - 1 vote

5 Comments on “Create Custom Reports with Labels of Asset Intelligence”

  1. Thought-provoking writing – I was fascinated by the insight , Does anyone know if I could possibly find a fillable a form version to type on ?

  2. Hello, Nicolas Excellent post. I was testing on a customer and found the table (v_LU_SoftwareList_Editable), it has the SCCM 2012 R2 CU 2. I went to see on another customer who also has the SCCM 2012 R2 CU4 and ok. The table exists. What may be that an environment exists and the other does not, any idea?
    Thank you.

    1. Hi Rodrigo,

      Did you ever experiment the situation where v_LU_SoftwareList_Editable was not present in the database?

      This view was also present in SCCM 2007.

      Thanks for your comment.

      Nick

Leave a Reply

Your email address will not be published. Required fields are marked *