SQL Queries to help find Agent Health issues

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

SQL Queries to help find Agent Health issues

Resolution

-- 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