SCCM query to create Asset Inventory Report

  • Article

SCCM query to prepare TCO report/Asset Inventory report within SCCM environment:

Select distinct  a.name0,a.user_name0, a.operating_system_name_and0, a.ad_site_name0,
b.manufacturer0,b.model0,c.releasedate0,c.serialnumber0,e.size0,
f.csdversion0,g.currentclockspeed0,h.totalphysicalmemory0 
from v_r_system a,v_GS_COMPUTER_SYSTEM b,v_GS_PC_BIOS c,
v_GS_ADD_REMOVE_PROGRAMS d,v_GS_PARTITION e,v_GS_OPERATING_SYSTEM f,
v_GS_PROCESSOR g,v_GS_X86_PC_MEMORY h 
where (a.resourceid=b.resourceid and b.resourceid=c.resourceid 
and c.resourceid=d.resourceid and d.resourceid=e.resourceid 
and e.resourceid=f.resourceid and f.resourceid=g.resourceid
and g.resourceid=h.resourceid)and a.operating_system_name_and0 like '%workstation%'

Above is for all devices in SCCM that are classified as workstations. Instead, if we want this report for all systems, the code below should be used:
*
*

Select distinct  a.name0,a.user_name0, a.operating_system_name_and0, a.ad_site_name0,
b.manufacturer0,b.model0,c.releasedate0,c.serialnumber0,e.size0,
f.csdversion0,g.currentclockspeed0,h.totalphysicalmemory0 
from v_r_system a,v_GS_COMPUTER_SYSTEM b,v_GS_PC_BIOS c,
v_GS_ADD_REMOVE_PROGRAMS d,v_GS_PARTITION e,v_GS_OPERATING_SYSTEM f,
v_GS_PROCESSOR g,v_GS_X86_PC_MEMORY h 
where (a.resourceid=b.resourceid and b.resourceid=c.resourceid 
and c.resourceid=d.resourceid and d.resourceid=e.resourceid 
and e.resourceid=f.resourceid and f.resourceid=g.resourceid
and g.resourceid=h.resourceid)