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:

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

SCCM2012Reports01

Now make sure you select “SQL-based Report”. Give it a proper name and browse to a folder you want to save it.

SCCM2012Reports02

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.

SCCM2012Reports03

SCCM2012Reports04

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.

SCCM2012Reports05

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.

SCCM2012Reports06

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.

SCCM2012Reports07

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:

[sourcecode language=”sql” gutter=”true” wraplines=”false” light=”false” htmlscript=”false” collapse=”false” padlinenumbers=”true”]
SELECT
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.Caption0,
v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘Installation date’
FROM
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
WHERE
(vWorkstationStatus.OperatingSystem not like N’%server %’)
Order by ‘Computer name’, Caption0 ASC;
[/sourcecode]

SCCM2012Reports08

This is optional, but I like to remove subtotals and grand totals. Click next.

SCCM2012Reports09

Choose a style. I often go fore the generic once as I like to customize the reports by myself. Click Finish.

SCCM2012Reports10

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:

SCCM2012Reports11

From SCCM 2012:

SCCM2012Reports12

Happy reporting! 😉