cancel
Showing results for 
Search instead for 
Did you mean: 
patriot
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
patriot
Level 12

Re: Need a custom SQL report for discovery results

Bump. This is an urgent need. Thank you.

0 Kudos
Highlighted
patriot
Level 12

Re: Need a custom SQL report for discovery results

Still urgent. No takers?

0 Kudos
mharvey
Level 17

Re: Need a custom SQL report for discovery results

I'm not sure that all of this is going to be possible.  If some of this data does not live in the SQL database, then it's not going to be possible to pull it into a report.  Now, there should be a way to get some of that information through joining a couple of the discovery tables to the nodes table in a query.  Something I have that might help is:

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.Hostname as Node_Name,

DiscoveryProfiles.EngineID as Poller,

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

Regards,

Matthew Harvey

Loop1 Systems

http://www.loop1systems.com

patriot
Level 12

Re: Need a custom SQL report for discovery results

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
mharvey
Level 17

Re: Need a custom SQL report for discovery results

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
zzz
Level 11

Re: Need a custom SQL report for discovery results

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
karltbraun
Level 9

Re: Need a custom SQL report for discovery results

I just want to add myself to the list of those who want something like this.  I would like to know what nodes are added to discovery for the last <period of time>.  I think this is an import review point.  Is there nothing that provides that kind of information?

0 Kudos
Jörg
Level 8

Re: Need a custom SQL report for discovery results

Hi there!

I'd like to bump this post once again as I am searching for the same information...

Maybe the Solarwinds tech staff could hook in and reveal how the new/changed/as-is information is calculated from the tables

Kind regards, Jörg

0 Kudos
ngx_ssg
Level 7

Re: Need a custom SQL report for discovery results

+1

It would be great to have a solution for this.

0 Kudos