SQL Queries to help find Agent Health issues
-- Systems that don't have latest Inventory Agent
select c.Name, c.[IP Address], c.[OS Name], ca.[Product Version] as 'Inventory Agent Version', *
from vComputer c
join inv_AeX_AC_Client_Agent ca
on ca._ResourceGuid = c.Guid
where [Agent Name] = 'Altiris Inventory Agent'
and [Product Version] < '8.1.5267'
-- Old systems that haven't checked in recently (no data in Client Config Request table)
select *
from vComputer c
left join dbo.Evt_NS_Client_Config_Request cr
on c.Guid = cr.ResourceGuid
where cr._ResourceGuid is null
and c.IsManaged = 1
and c.CreatedDate < GETDATE() - 30
-- Systems that haven't requested configuration update for 30 days
select i.Name, cr.StartTime
from dbo.Evt_NS_Client_Config_Request cr
join vItem i
on i.Guid = cr.ResourceGuid
where cr.StartTime in (
select max(StartTime)
from Evt_NS_Client_Config_Request
where cr.StartTime < GETDATE() - 30
group by resourceguid
)
order by cr.StartTime desc
--Systems that have requested configuration update in last two days
select i.Name, cr.StartTime
from dbo.Evt_NS_Client_Config_Request cr
join vItem i
on i.Guid = cr.ResourceGuid
where cr.StartTime in (
select max(StartTime)
from Evt_NS_Client_Config_Request
where cr.StartTime > GETDATE() - 2
group by resourceguid
)
order by cr.StartTime desc