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 9

is there a way to ignore any new discoveries that already been mark ignore  on this report ?

0 Kudos
Level 9

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.SysName as Node_Name,

Discoverednodes.IPAddress,

DiscoveryProfiles.EngineID as Poller,

DiscoveryProfiles.LastRun as LastRun

From DiscoveredNodes

Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID

Where IPAddress NOT IN (Select IP_Address from Nodes) AND

IPAddress Not in (Select IPAddress from NodeIPAddresses)

AND Sysname NOT IN (Select Caption from Nodes)

AND SysName NOT IN (Select DNS from Nodes)

AND IPAddress NOT IN (Select IPAddress from DiscoveryIgnoredNodes)

I was able ignore the ignore list on this report

0 Kudos
Level 9

Thanks for the report, this is the best thing I have found and this was a post from five years ago!  Has there been any new developments around a built in report for this capability? 

0 Kudos
Level 8

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

+1

It would be great to have a solution for this.

0 Kudos
Level 9

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

Bump. This is an urgent need. Thank you.

0 Kudos
Level 12

Still urgent. No takers?

0 Kudos
Level 17

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

Level 12

mharvey,

I think I am looking for NodeIDs that are in the DiscoveredNodes table, but NOT in the Nodes table, correct? I made a quick examination of the NodeIDs in those two tables and saw some that were the same and some that were not.

So, how can I list nodes that have been discovered, but not yet added to the database?

0 Kudos
Level 17

You'd need to add this as a Where statement

Where DiscoveredNodes.NodeID NOT IN (Select Nodes.NodeID from Nodes)

This should only show those nodes that are not in there.

0 Kudos
Level 12

Thanks, but when I add that filter and then spot check the IP addresses that appear in the report, I am finding that they ARE in the database.

0 Kudos
Level 17

I could see that with the face we're joining on the nodes table.  Effectively I believe that anything in that query would be in the nodes table now that I think on it further. 

Try:

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.Hostname as Node_Name,

DiscoveryProfiles.EngineID as Poller,

Engines.ServerName as PollerName,

DiscoveryProfiles.LastRun as LastRun

From DiscoveredNodes

Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID

Join Engines on DiscoveryProfiles.EngineID = Engines.EngineID

Where DiscoveredNodes.NodeID NOT IN (Select NodeID from Nodes)

0 Kudos
Level 12

Still seeing nodes that are have been added to the DB.

0 Kudos
Level 17

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.Hostname as Node_Name,

DiscoveryProfiles.EngineID as Poller,

DiscoveryProfiles.LastRun as LastRun

From DiscoveredNodes

Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID

Where DiscoveredNodes.NodeID NOT IN (Select NodeID from Nodes)

0 Kudos
Level 12

Looks like the Where statement is not working, because I am still seeing nodes that HAVE been added to the database appear in this report. Are you able to test this SQL in your environment?

0 Kudos
Level 17

Turns out the nodeID wasn't what the whereclause needed.  Using sysname seems to have proven better results.

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.SysName as Node_Name,

DiscoveryProfiles.EngineID as Poller,

DiscoveryProfiles.LastRun as LastRun

From DiscoveredNodes

Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID

Where SysName not in (Select Caption from Nodes)

Feel free to make any other adjustments running this in your environment in order to fine tune to meet your needs.

0 Kudos
Level 17

Update:  Due to the changes and additions in NPM there are some other filters added in the WhereClause to ensure accuracy. 

Select

DiscoveryProfiles.Name as Discovery_Name,

DiscoveredNodes.SysName as Node_Name,

Discoverednodes.IPAddress,

DiscoveryProfiles.EngineID as Poller,

DiscoveryProfiles.LastRun as LastRun

From DiscoveredNodes

Join DiscoveryProfiles on DiscoveredNodes.ProfileID = DiscoveryProfiles.ProfileID

Where IPAddress NOT IN (Select IP_Address from Nodes) AND

IPAddress Not in (Select IPAddress from NodeIPAddresses)

AND Sysname NOT IN (Select Caption from Nodes)

AND SysName NOT IN (Select DNS from Nodes)

0 Kudos
Level 11

I haven't had a chance to look too deeply into this to confirm my thoughts of what each status means, but I think you may want to take a look at the discoverednetobjectstatuses table in order to determine whether it is newly found or changed.  Guessing at it, it looks like a status 32 is either ignored or not added and not in the system but is a newly found node.  A status 33 makes me think that indicates a change to the Node and a statuts 1 looks to mean added.  The table has ProfileID and DiscoveredObjectID that can be joined on.  Setup a case statement on the status at that point.  I wish I had some more time to put the SQL together, but I hope my suspicious are correct and this helps get it worked out.

0 Kudos
Level 12

Now we seem to be getting somewhere. However, I found that the most recent iteration shows no nodes that are in the DB, but is missing some nodes that should be on the report. I found that if I delete the last two lines in the Where clause, the list of IPs in the report matches the list of FOUND nodes on the Scheduled Discovery Results tab. Does that make sense?

0 Kudos