SMS SQL for Last Hardware Inventory

The following SQL Query will return last HW Inventory date for a specific computer. Code is based on the client health queries by Paul Thomsen.

-- Last hardware inventory

select Name0, LastHWScan as 'HW Inventory'
from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID
full join v_GS_WORKSTATION_STATUS WS ON WS.resourceID=sys.resourceID
where client0=1
and Name0 = 'computername'

SMS SQL for Last Discovery

The following SQL Query will return last discovery date for a specific computer. Code is based on the client health queries by Paul Thomsen.

-- Last discovery date

select Name0, AgentTime as 'Discovery'
from v_R_System sys full join v_RA_System_SMSAssignedSites ass ON ass.resourceID=SYS.resourceID full join (select ResourceId, MAX(AgentTime) as AgentTime from v_AgentDiscoveries where agentname<>'SMS Discovery Data Manager' AND agentname not like '%!_AD!_System%' ESCAPE'!' group by ResourceId) disc on disc.resourceid=sys.resourceid
where client0=1 --and IsNULL(AgentTime,@NullVal)>@olddate
and Name0 = 'computername'