需求背景

为便于资产管理员收集当前所有用户使用的计算机的硬件信息,通过SCCM数据库查询实现获取当前所有计算机硬件、当前使用人。减少人工维护成本。


SQL查询语句

 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/1024000.0 as 'Total Memory', 
 sum(isnull(LDisk.Size0,'0'))/1024.0 as 'Hardrive Size', 
 sum(isnull(LDisk.FreeSpace0,'0'))/1024.0 AS 'Free Space', 
CPU.Name0 AS 'CPU Name'
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.Name0

结果展示


延展思考

基于第三方开源的固定资产管理系统,将现有SCCM数据库中的硬件、软件、人等数据传输到中间库,然后再计划任务定期导入至固定资产系统数据库中。(未完,后续更新)

通过SCCM数据库获取计算机硬件报告
通过SCCM数据库获取计算机硬件报告
通过SCCM数据库获取计算机硬件报告