SCCM 2012 Reporting – Creating Custom reports using SQL Query

In Configuration manager 2012 we do not have an out of the box report which shows the system serial number.

This step by step guide will show you how to setup your custom report using existing SQL Query.

Open SCCM console and open the Monitoring > Overview > Reporting> Right click the “Reports” node and click onCreate Report.

Open SCCM console and open the Monitoring > Overview > Reporting> Right click the “Reports” node and click onCreate Report.

Click on SQL- Based Report

Click to Browse the category to save the report in 

Click OK and then Click Next till you reach  summary page and then close after the report is created.

When you finish the report the SQL Server Report Builder should now open.

We’ll start this report by creating a “Table or Matrix” so click on that icon. 
We now need to enter in a dataset for the report to use so leave the “Create a dataset” selected and click Next. 
You’ll now be prompted by a “Choose a connection to data source” page. 
Click next whilst the highlight is on the server that hosts your reporting point.

If you already see data source connection, just hit Test Connection to verify it If not, then click Browse Enter in the credentials that have access to the SCCM database and click Next.

You’ll now hit the “Design a query”  Click on Edit as Text in the top left corner.

Paste your query and hit red color Exclamation Mark just next to import

Select sys.Name0 Machine, CS.UserName0 'User Name',OS.Caption0 OS, OS.CSDVersion0 'Service Pack',CS.Manufacturer0 Manufacturer,CS.Model0 Model,BIOS.SerialNumber0 'Serial Number',RAM.Capacity0 Memory, cs.SystemType0

From v_R_System SYS

  left join v_GS_COMPUTER_SYSTEM CS on sys.Name0=cs.Name0

  left join v_GS_PC_BIOS BIOS on sys.ResourceID=bios.ResourceID

  left join v_GS_OPERATING_SYSTEM OS on sys.ResourceID=OS.ResourceID

  left join v_GS_PHYSICAL_MEMORY RAM on sys.ResourceID=ram.ResourceID

  --Where sys.Name0 = @variable

  order by sys.Name0

On the next screen, you’ll get the ability to lay out how you want your report to look. I’ve selected all the fields from available and drag it over ∑ values box

Click next on Choose the layout page

Click Next and then Finish. You should be back at the report builder screen. You can start modifying the report to look exactly how you’d like

You can always go back to the design by clicking Design if you need to make modifications or add extra information to this report like below.

On the design page if you want to put header click above the report and insert the Text Box and type the report name as you like

If you want to bring the total number of machines on this page, insert new Text box just next to the report header. Right click inside the box click Expression

Paste this query

=Count(Fields!Machine.Value, "DataSet1")

After you adjusting all the fields the way you want and when all this is done, click on Run to view the report

Click on Save icon, you should be able to see the reports under “Site – General” you should now see your new report. This should  appear in the console as well as the web reports.

Thanks this Blog for the solution: http://blogs.technet.com/b/infratalks/archive/2013/09/10/sccm-2012-reporting-get-system-serial-number-amp-create-custom-ssrs-report.aspx