This SQL query will allow you to find or locate machines in your SMS database that have not reported a Heartbeat discovery record in the last week or seven days.
SQL Query:
Select
SD.Netbios_Name0,
A.AgentName,
DI.AgentTime
From System_Disc SD
Inner Join DiscItemAgents DI on SD.DiscArchKey = DI.DiscArchKey
Cross Join Agents A
Where A.AgentName = 'Heartbeat Discovery'
And DatePart (D,DI.AgentTime) >= 7
Doesn't work. I get a message saying that this query has a syntax error.
Never mind... I have overlooked the "SQL" in there. Do you know how to create collection based on similar criteria?
Well I think I have found it:
select
SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from
SMS_R_System
where
ResourceId not in (select ResourceID from
SMS_R_System where AgentName in ("Heartbeat Discovery") and
DATEDIFF(day,AgentTime,GetDate())<=14)