cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Need a custom SQL report for discovery results

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?

Tags (3)
24 Replies
Level 17

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. 

0 Kudos
Level 12

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.

0 Kudos
Level 17

Adding the name of the poller is easy.

Select

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

From DiscoveredNodes

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.

0 Kudos
Level 11

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.

0 Kudos