I am not a SQL expert, so I am having a little trouble with a report that I would like to create for scheduled discovery results.
I want a report that includes node name, IP address, engine ID, discovery profile name, identifies Found vs Changed, when discovery ran, etc. However, the tables in the database do not seem to contain all of these fields.
Has anyone configured such a report? Thank you for any help?
the last two lines are merely comparing the Sysname in the Discovered Nodes table to the Caption and DNS fields in the nodes table and ensuring it doesn't match either in order to show up in the report.
Thanks, mharvey. That is most of what I need. I still need to know if a listed node was designated as Found or Changed, and I also want to list the polling server name as well as the Engine ID. Thanks for your help.
Adding the name of the poller is easy.
DiscoveryProfiles.Name as Discovery_Name,
DiscoveredNodes.Hostname as Node_Name,
DiscoveryProfiles.EngineID as Poller,
Engines.ServerName as PollerName,
Nodes.IP_Address as IPAddress,
DiscoveryProfiles.LastRun as LastRun
Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID
Join Nodes On Nodes.SysName = DiscoveredNodes.SysName
Join Engines on DiscoveryProfiles.EngineID = Engines.EngineID
As far as changed vs. found. Looking at the tables, there is nothing that identifies this that I can see as to whether a node was new or previously in the database, as such there doesn't seem to be any way to write anything to show that in the report. And since there is nothing like a Date Added value for nodes, you can't really compare that against the last run of the discovery to use something where if those dates were equal they were added and if the date added was earlier it would be seen as changed. I'm not thinking there is going to be a way to get that.
Technically, you can just go by the NodeID.
It always is incrementing in order of node creation- even if you delete a node, it doesn't fill back the space.
Record the largest NodeID, then just have a True/False if it is greater than your listed NodeID to see if it was added afterwards.
Of course, this would mean you need to constantly record and change the last known NodeID.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.