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

Re: Need a custom SQL report for discovery results

Bump. This is an urgent need. Thank you.

0 Kudos
Highlighted
Level 12

Re: Need a custom SQL report for discovery results

Still urgent. No takers?

0 Kudos
Highlighted
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

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