How to: Create custom SQL- based Reports in System Center 2012 R2: Configuration Manager

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

About the Author:

Marius A. Skovli
Microsoft Enterprise Client Management Evangelist with: 10+ years experience within Microsoft System Management Solutions Extensive experience across Private and Public Sector Passion for Community Driven work, volunteering within Microsoft technology Great belief that sharing experience within fellow peers is key to creating a sustainable society Strong commitment to System Center User Group Norway as co-founder and current leader I am a technology enthusiast working as a consultant for the consultant company CTGlobal. I have always been passionate about IT and have the last 10 + years worked with Management and Automation within Microsoft technology. Back in 2005/6 I started working with System Management Server (SMS) 2003 and have been working with Enterprise Client Management ever since, where i today focus on helping customers design and implement solutions based on System Center Configuration Manager and/or Enterprise Mobility Suite from Microsoft. Other parts of my work consists of speaking and presenting at different events and seminars, doing research and blog about solutions I find and products I work with. I truly believe in a strong community where knowledge and know-how is essential. Creating creative arenas where it is possible for peers to spread the word about new technologies and solutions is key and as an act on this I co-founded System Center User Group Norway (www.scug.no). SCUG is an initiative where we discuss, preach and present new technologies and solutions in the System Center Space from Microsoft. This is a free arena for everybody to join that is interested in/or enthusiastic about Microsoft Cloud Platform (Enterprise Client Management or Cloud and Datacenter). Specialties: System Center Configuration Manager (SCCM2007-SCCM2012), Enterprise Mobility and Intune, Windows and Windows server deployment.

One Comment

  1. Matt January 25, 2017 at 22:34 - Reply

    Thanks so much for taking the time to share! Straight forward and easy to follow!

Leave A Comment