This SQL query will allow you to locate those machines that are in need of remediation for your site(s).
The script is in many ways similar to the process that the client health tool uses in that it checks timestamps for the last hardware and software scan dates as well as the time stamp for the last Heart beat DDR based on a criterion of days.
SQL Query:
Select
SD.Name0 'Machine Name',
SC.SMS_Assigned_Sites0 'Assigned Site',
'Obsolete' = Case
When SD.Obsolete0 = 0 Then 'NO'
When SD.Obsolete0 = 1 Then 'YES'
Else ' '
End,
'Active' = Case
When SD.Active0 = 0 Then 'NO'
When SD.Active0 = 1 Then 'YES'
Convert(VarChar(10), HW.LastHWScan, 101) 'Last Hardware Scan',
Convert(VarChar(10), SW.LastScanDate, 101) 'Last Software Scan',
Convert(VarChar(10), AD.AgentTime, 101) 'Last DDR Update'
From v_R_System SD
Join v_RA_System_SMSAssignedSites SC on SD.ResourceID = SC.ResourceID
Join v_GS_WORKSTATION_STATUS HW on SD.ResourceID = HW.ResourceID
Join v_GS_LastSoftwareScan SW on SD.ResourceID = SW.ResourceID
Join v_AgentDiscoveries AD on SD.ResourceID = AD.ResourceId
Where AD.AgentName = 'Heartbeat Discovery'
And (DateDiff(D, AD.AgentTime, GetDate()) >= 7)
Note: Change the Date (7) to the number of days needed. For example to be less specific change the value to 30 days or to be more specific change the value to 2 days and so on.
No Comments