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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

0 Kudos
mharvey
Level 17

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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

Re: Need a custom SQL report for discovery results

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