Create Custom Reports in SCCM based on SQL Queries

Today I had a request from a customer to create a custom report in Configuration Manager to meet their specific requirements.  Specifically, a report that would include:

  • Computer Name
  • Domain
  • User
  • Serial Number
  • Make and Model
  • OS
  • Site Code
  • Memory
  • Hard disk size
  • CPU Speed

First I needed to find a SQL query that would fulfil the above criteria.  I used the following query from the SMSUG.CA blog found here

SELECT  distinct 

 CS.name0 as ‘Computer Name’, 
 CS.domain0 as ‘Domain’, 
 CS.UserName0 as ‘User’, 
 BIOS.SerialNumber0 as ‘Bios serial’, 
 SE.SerialNumber0 as ‘System Enclosure serial’,
 CS.Manufacturer0 as ‘Manufacturer’, 
 CS.Model0 as ‘model’, 
 OS.Caption0 as ‘OS’, 
 RAA.SMS_Assigned_Sites0 as ‘Site’, 
 RAM.TotalPhysicalMemory0 as ‘Total Memory’, 
 sum(isnull(LDisk.Size0,’0′)) as ‘Hardrive Size’, 
 sum(isnull(LDisk.FreeSpace0,’0′)) AS ‘Free Space’, 
 CPU.CurrentClockSpeed0 as ‘CPU Speed’ 
from  
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID  
 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
 right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID    
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID 
where 
 LDisk.DriveType0 =3
group by 
 CS.Name0, 
 CS.domain0,
 CS.Username0, 
 BIOS.SerialNumber0, 
 SE.SerialNumber0,
 CS.Manufacturer0, 
 CS.Model0, 
 OS.Caption0, 
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0, 
 CPU.CurrentClockSpeed0

 

Next I needed to create a custom report in Config Manager based on the above SQL query.  I followed the procedure detailed in this blog substituting the SQL query above.

 This worked a treat, creating a custom report in minutes, specific to my customers’ requirements

CustomReport