How to create a report/collection based on Last Configuration Request of client machines

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Create either a collection or SQL Report using the following SQL Query to create a Report/Collection based on the Last Configuration Request of a client machine

SELECT
a.[Guid],
a.[Name] AS 'Computer Name',
a.[User],
a.[OS name] AS 'Operating System',
c.[LastConfig] AS 'Days Since Last Check In',
c.[LastConfigDate] AS 'Last Configuration Request Date'
FROM vComputer a with (NOLOCK)
LEFT JOIN(SELECT ResourceGuid, LastConfig = MIN(datediff(dd,[StartTime],getdate())), LastConfigDate = MAX([StartTime])
FROM Evt_NS_Client_Config_Request with (NOLOCK)
GROUP BY ResourceGuid) AS c ON c.[ResourceGuid] = a.[Guid]
WHERE c.[LastConfig] > 25 -- number of days
ORDER BY c.[LastConfig] DESC

 

 

This SQL will list all the clients based on last agent check in date.