Hardware inventory Report for SCCM 2012

以下的語法 僅供參考 你可以使用RB3.0 或者直接在MS SQL下指令,取得你要的報表
Below example just for your reference!! You could use RM3.0 to build up your own report or you could run this in your sql server of sccm.

SELECT v_R_System.Netbios_Name0, CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE ‘vmware%’ THEN ‘VM’ ELSE v_GS_PC_BIOS.SerialNumber0 END AS [Serial No],
v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor],
CASE WHEN v_GS_PROCESSOR.ADDRESSWIDTH0 = ’32’ THEN ’32 Bit’ ELSE ’64 Bit’ END AS [Processor Type],
CASE WHEN v_GS_PROCESSOR.IS64BIT0 = ‘1’ THEN ‘Yes’ ELSE ‘No’ END AS [64 Bit Supported],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)],CASE WHEN v_GS_DISK.MediaType0 = ‘Fixed hard disk media’ THEN ‘Fixed Hard Disk’ ELSE ‘Portable Drive’ END AS [Hard Disk Type], v_GS_DISK.Model0 AS [Disk Model], v_GS_DISK.Size0 AS [DISK SIZE], c.IPAddress AS [IP Address], v_GS_Operating_System.Caption0 AS [Operating system],
v_GS_Operating_System.Version0 AS [Operating system version], v_GS_OPERATING_SYSTEM.ServicePackMajorVersion0, CONVERT(nvarchar(10),
v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [Installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On],
v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0
(SELECT t1.resourceid AS rsid2, IPAddress = substring
((SELECT (IP_Addresses0 + ‘, ‘)
FROM v_RA_System_IPAddresses t2
WHERE t2.IP_Addresses0 NOT LIKE ‘169%’ AND IP_Addresses0 NOT LIKE ‘0.%’ AND IP_Addresses0 NOT LIKE ‘%::%’ AND t1.resourceid = t2.resourceid
ORDER BY resourceid FOR xml path(”)), 1, 50000)
FROM v_RA_System_IPAddresses t1
GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN

v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN
v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN
v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID JOIN
v_GS_DISK ON v_GS_DISK.ResourceID = v_GS_PROCESSOR.ResourceID 
ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0,
v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0,

Print Friendly, PDF & Email