This is a handy little SQL statement we managed to whip up while working on a discovery problem. This script will show you a count of Machine based discoveries (not user) per site and per discovery type. Output will look something like this:
| Count |
Site Code |
Discovery Type |
| 1999 |
001 |
Heartbeat |
| 200 |
001 |
AD_System_Discovery |
| 3000 |
002 |
AD_System_Group |
Basically it will give you a much better chance of finding run-away discoveries in your infrastructure….discoveries that have been set too low or aren’t set frequently enough. You can also narrow it down to a smaller collection, if you wish. To do so, change the ‘SMS00001’ collection ID.
The @variable is the number of days you want counts for. Setting it to 30, for example, would show you the discovery counts from the last month.
Enjoy!
-----SCRIPT BELOW HERE-----
declare @variable as int
set @variable = '7'
SELECT count(agent.resourceid),fcm.SiteCode,Agent.AgentName
FROM v_AgentDiscoveries Agent
JOIN v_R_System SYS ON Agent.ResourceId = SYS.ResourceID
JOIN v_FullCollectionMembership fcm on SYS.ResourceID=fcm.ResourceID
WHERE @variable >= DateDiff(Day,Agent.AgentTime,GetDate()) and fcm.CollectionID = 'SMS00001'
GROUP BY Agent.AgentName,fcm.SiteCode
ORDER By count (sys.resourceid) desc
-----SCRIPT ABOVE HERE-----