I got this request from a customer and thought I could share it. Below query describes how to modify the query for the built-in report “Computer information for a specific computer” to include serial number. The added sql commands are marked in red color if you want to change it yourself instead of copying the SQL syntax.
SELECT SYS.Netbios_Name0, SYS.User_Name0, SYS.User_Domain0, SYS.Resource_Domain_OR_Workgr0,
OPSYS.Caption0 as C054, OPSYS.Version0,
MEM.TotalPhysicalMemory0, IPAddr.IP_Addresses0, Processor.Manufacturer0,
CSYS.Model0, v_GS_PC_BIOS.SerialNumber0, Processor.Name0, Processor.MaxClockSpeed0
FROM v_R_System SYS
LEFT JOIN v_RA_System_IPAddresses IPAddr on SYS.ResourceID = IPAddr.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_PC_BIOS on SYS.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
WHERE SYS.Netbios_Name0 = @variable
ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0
Enjoy!
Hello – when trying to edit the report with the red text you mentioned, I get “the multi-part identifier “BIOS.SerialNumber0″ could not be bound” (Microsoft SQL Server, Error: 4104″ – this is with SCCM 1902. Perhaps things have changed since when you wrote this useful document. Thank you.