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.
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.
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.
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.
- 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. **
- 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.
- 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
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
Happy with the results? Let’s deliver the report.
- Click the Save button on the top left.
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.
- Open your web browser, go to your reporting website (usually http://nameoftheserver/reports) and discover the report in the right location
- 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
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.
Pingback: asset management system screenshot | nangnung.tk
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/
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 ?
Hello to every one, the contents existing at this web site are genuinely amazing for people knowledge, well, keep up the nice
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?
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.