A customer asked me for a “how-to” on how to Create reports in SCCM 2012, so why not share with everyone. This post is not intended to show how to write or design queries, but show you have to create a report based on a SQL query you might already have. Even-though we are skipping how to write SQL Queries for know, this post is still going to be a bit long. Time for that cup of coffee!
First some requirements:
- You need to have an SQL Server instance for SCCM with SQL Reporting Services running
- You need to have the SCCM Reporting Services Point role installed and configured which provided integration with SQL Server Reporting Services
- The Reporting Services Point Account need to have read-rights to the Reporting database on the SCCM SQL Instance.
- To see all requirements go to Microsoft TechNet: http://technet.microsoft.com/en-us/library/gg712698.aspx
- To see how to add the role go to Windows Noob: http://www.windows-noob.com/forums/index.php?/topic/4550-using-sccm-2012-rc-in-a-lab-part-11-adding-the-reporting-services-point-role
When all of the above is configured properly you should end up with a bunch of reports under Monitoring – Reporting – Reports.
Lets get started!
Open up your SCCM 2012 Console and navigate to monitoring. Then go to reports, right click and then click “Create Report”.
Now make sure you select “SQL-based Report”. Give it a proper name and browse to a folder you want to save it.
Now just click next, next, close. After you close a IE-window will automatically open. If you need to authenticate do so and add the URL to trusted-sites. The next thing that will happen is SQL Reporting Builder will either be installed or it will start depending on if it has been installed earlier or not.
When you enter the SQL Server Reporting Builder you get a Office “Look and Feel” GUI. Here we can customize all the reports that already exists in SCCM and alle the new ones we are about to create! 🙂
The first thing we need to do is the create a New Table, so click on “New table or Matrix”. You should get a view similar to the one below. Next make sure that “create dataset” is checked on and click next.
On the next screen choose Data Source Connection. There may be that you will have to authenticate. Choose either to authenticate with current Windows User or type another username and password.
Click on “Edit as Text” and paste your SQL Query. Hit execute (!) to test if your query works and presents the data your are looking for. Click Next.
Arrange the different fields as you like and then click next. Also, time to honor yourself with a new cup of coffee!
Example query if you do not have one:
vWorkstationStatus.Name AS ‘Computer name’,
vWorkstationStatus.UserName AS ‘User name’,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS ‘Manufacturer’,
v_GS_COMPUTER_SYSTEM.Model0 AS ‘Model’,
v_GS_PC_BIOS.SerialNumber0 AS ‘Serialnumber’, CONVERT(varchar(20),
v_GS_OPERATING_SYSTEM.LastBootUpTime0, 100) AS ‘Last Boot’,
v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘Installation date’
vWorkstationStatus INNER JOIN
v_GS_PC_BIOS ON vWorkstationStatus.ResourceID = v_GS_PC_BIOS.ResourceID INNER JOIN
v_GS_OPERATING_SYSTEM ON vWorkstationStatus.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
(vWorkstationStatus.OperatingSystem not like N’%server %’)
Order by ‘Computer name’, Caption0 ASC;
This is optional, but I like to remove subtotals and grand totals. Click next.
Choose a style. I often go fore the generic once as I like to customize the reports by myself. Click Finish.
Finally customize your report as you wish. The reporting builder is not so smooth as Word or any other Office product, but after playing with the product you should be able to create some good looking reports! After your are finished, remember to save your report. Underneath is an example of how you can do it if you are in a hurry! 😉
In the Reporting Builder:
From SCCM 2012:
Happy reporting! 😉