Create Report in SCCM with Computer Information

Written by Jeroen Tielen on . Posted in SCCM

This how-to shows how to create a report in System Center Configuration Manager with computer information like serial number etc.

Open the Management Console. Navigate to Reporting. Right click on Reports and select New –> Report.  image
Give it a name.

Select the category under which the report will be displayed. (eg Hardware – General)

Click on: Edit SQL Statement.

image
Paste the Statement(displayed and the bottom of this blog post.) in the SQL Statement box.

Remove the default statement.

image
Run the report and it will display al kind off useful stuff. Like the Serial Number. Knipogende emoticon image


Q: How do we read  the systems serial number with SCCM?

A: Create custom report with the following SQL Statement:

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

Tags: , , , , , ,

Trackback from your site.

Comments (1)

  • kksoe

    |

    Hi Jeroen Tielen,

    I received below error when i create custom report wity your SQL Statement .

    [42000][102]Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect syntax near".
    ---------------------------------------------
    Please help to check below my SQL Statement. Thanks you for any help you can provide.

    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

    Reply

Leave a comment

New Layout

Due to the new layout, some posts are not that readable anymore ;) Working on that. So be patient, or select all and paste into notepad or something. ;)