29 November 2006

How can I find active computers which aren't sending Patch Management Inventory Summary data?

Create a new report, and choose to enter the SQL manually. Copy and paste the following:
________________________________


SELECT vC.[Name], vC.[Domain], vC.[OS Name], vC.[OS Type], a.LastConfig, vC.[Guid]
FROM vComputerEx vC
INNER JOIN Inv_AeX_AC_Client_Agent acca
ON vC.[Guid] = acca.[_ResourceGuid]
INNER JOIN (
SELECT ResourceGuid, LastConfig = datediff(dd,MAX([StartTime]),GETDATE()),
LastConfigDate = MAX([StartTime])
FROM Evt_NS_Client_Config_Request
GROUP BY ResourceGuid
HAVING DATEDIFF(dd,MAX([StartTime]),GETDATE()) <= 30 --change to a lower number if desired
) AS a
ON a.ResourceGuid = vc.Guid
WHERE 1 = 1
AND vC.[Guid] NOT IN (
SELECT DISTINCT _ResourceGuid
FROM Evt_Inventory_Rule_Summary
)
AND acca.[Agent Name] = 'Altiris Software Update Agent'

________________________________


The results of this report will be all machines which have requested configuration from the NS within the last 30 days but have not sent an Inventory Rule summary (or the Inventory was rejected by the NS for some reason due to some problem).

1 comment:

Anonymous said...

SELECT vC.[Name],
vC.[Domain],
vC.[OS Name],
vC.[OS Type],
a.LastConfig,
vC.[Guid]
FROM vComputerEx AS vC
INNER JOIN Inv_AeX_AC_Client_Agent AS acca ON vC.[Guid] = acca.[_ResourceGuid]
AND acca.[Agent Name] = 'Altiris Software Update Agent'
INNER JOIN (
SELECT ResourceGuid,
LastConfig = datediff(dd,MAX([StartTime]),GETDATE()),
LastConfigDate = MAX([StartTime])
FROM Evt_NS_Client_Config_Request
WHERE [StartTime] > DATEDIFF(DAY, 30, GETDATE())
GROUP BY ResourceGuid
) AS a ON a.ResourceGuid = vc.Guid
WHERE NOT EXISTS (SELECT * FROM Evt_Inventory_Rule_Summary WHERE vC.[Guid] = _ResourceGuid)