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.

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 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”/]

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 the Asset Intelligence folder
  • Click Next

sccm 2012 custom reports

  • Confirm details summary and click Next

sccm 2012 custom reports

  • If the Create Report Wizard succeeds, you should have a 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 the minimum requirement of creating reports. A data source is the database configuration and a dataset is the query used for the report.

sccm 2012 custom reports

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 **

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

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 are 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 the 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 colour and modify the style.

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

sccm 2012 custom reports

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

sccm 2012 custom reports

sccm 2012 custom reports

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

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

sccm 2012 custom reports

Comments (8)

game xóc đĩa ăn tiền thật

06.08.2019 AT 06:58 AM
It's actually a great and helpful piece of info. I am glad that you just shared this helpful info with us. Please stay us informed like this. Thank you for sharing.

cartier love necklace fake

08.29.2017 AT 10:14 PM
I simply needed to say thanks once again. I am not sure what I could possibly have created without the entire hints contributed by you concerning such a theme. It truly was a real scary case in my position, nevertheless witnessing a expert technique you treated the issue made me to leap for gladness. I am happier for your help and in addition sincerely hope you are aware of a great job you’re carrying out teaching many others through a site. I know that you have never come across all of us. cartier love necklace fake http://www.classicaljewelry.net/

Brock Graham

05.10.2016 AT 10:19 PM
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 ?

ultrasound course

09.21.2015 AT 01:59 PM
Hello to every one, the contents existing at this web site are genuinely amazing for people knowledge, well, keep up the nice work fellows.

Nicolas Pilon

09.21.2015 AT 10:37 PM
Thanks! Really appreciated.

Rodrigo

02.16.2015 AT 05:08 AM
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.

Nicolas Pilon

02.16.2015 AT 04:52 PM
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