Saturday, February 9, 2013

SCCM report to get PC details

This is quick query to get PC details in an environment.

This report will include Name,  whether the device laptop or desktop,  Serial no, Manufacturer, Model, OS, SP level, last logged on user, total memory and total HDD


***********************************************************
SELECT  distinct
  CS.Manufacturer0 as 'Manufacturer',
  CS.Model0 as 'model',
    CASE
        WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
        WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
        Else 'Unknown'
END as 'Description',
 BIOS.SerialNumber0 as 'Bios serial',
 CS.name0 as 'Computer Name',
 RAM.TotalPhysicalMemory0 as 'Total Memory',
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
 OS.Caption0 as 'OS',
 CSDVersion0 as 'Service Pack',
 CS.UserName0 as 'User',
 CS.domain0 as 'Domain'
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_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on SYS.ResourceID = ES.ResourceID
where
 LDisk.DriveType0 =3
group by
  CS.Manufacturer0,
  CS.Model0,
  ChassisTypes0,
  BIOS.SerialNumber0,
  CS.Name0,
  RAM.TotalPhysicalMemory0,
  OS.Caption0,
  CSDVersion0,
  CS.Username0,
  CS.domain0
*********************************************************

More SCCM custom reports can be found here

5 comments:

  1. Do you know if there is a way we get BIOS details like whether a machine is TPM enabled or not??
    TIA
    Ramesh

    ReplyDelete
  2. Hi Ramesh,
    Please refer to the following link which may help you
    http://www.myitforum.com/forums/Creating-a-Query-to-determine-TPM-Chip-status-on-Laptops-m209158.aspx

    Good Luck,
    Venu Singireddy

    ReplyDelete
  3. How would I retrieve the full user name (AD Display name, it is available to SCCM) using this query. My boss likes this report, but would like to see who (AD display name/full username) of the last logged on user. I may want to add in the last heart beat discovery to show when the information was last updated. Thank you for the help!

    ReplyDelete
  4. Hi Venu,

    I am new to SCCM and tried running this as a query but it keeps prompting "This query has a syntax error. Are you sure you want to save it?". after saving it when I run the query, I get "Invalid query". Pls help. I am trying to display if the client is a laptop or Desktop in my query and I can only display the Chassis type number but not as a laptop or desktop.

    Thank you.
    Arun

    ReplyDelete
  5. Hi Arun,
    I tested and it is working fine for me.
    You copy the query notepad first then check whether there are any additional characters.
    Try running the query on SQL management studio.

    I am sure it will be something to do with copy and paste.
    Let me know how you go.
    Cheers
    Venu

    ReplyDelete