asked on

How to create a custom report in SCCM?

This is using MS SCCM 2012 R2 server. My boss want to have a full hardware inventory for all 150 managed client workstations. I drilled down to Monitoring workspace > Reporting > reports, and managed to find a report for hardware inventory that only specified for a single computer. However, I managed to get a script for all systems as follows:

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

How can I create a custom report using the above script? Please show step-by-step.

Thanks in advance.

ASKER CERTIFIED SOLUTION

Ganesh Anand

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MichaelBalack

ASKER

Hi Ganesh,

I don't really know how to get the report created. I thought back to SMS 2003 time, in which the report can be easily created.

Ganesh Anand

You are right it is not easy though like SMS 2003, those are olden golden days. The SCCM has been integrated along with SQL so the control has gone to SQL reporting service, But still SCCM 2012 provides the basic reports but it uses SQL db for extracting the reports. The default report provided is not useful for most of our requirement. They are generic, but if you have little bit query knowledge then you can proceed with modifying and changing the report structure.

MichaelBalack

ASKER

Hi Ganesh,

I managed to read through (and practicing) most of the steps as stated in your first suggested article. I gradually gain some understanding on how the query is about. Please give me more time so as I can finish the 2nd article.

thanks a lot.

MichaelBalack

ASKER

Thanks Ganesh to provide the suggested 2 articles. After practicing few times, I can understand how to create some simple queries. really appreciated.