Donnie Taylor at myITforum.com

Track System Discoveries per site

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:

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-----