21 Replies Latest reply on Nov 25, 2014 12:07 PM by mharvey

    Need a custom SQL report for discovery results

    patriot

      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?

        • Re: Need a custom SQL report for discovery results
          patriot

          Bump. This is an urgent need. Thank you.

            • Re: Need a custom SQL report for discovery results
              patriot

              Still urgent. No takers?

                • Re: Need a custom SQL report for discovery results
                  mharvey

                  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

                  1 of 1 people found this helpful
                    • Re: Need a custom SQL report for discovery results
                      patriot

                      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.

                        • Re: Need a custom SQL report for discovery results
                          mharvey

                          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.

                            • Re: Need a custom SQL report for discovery results
                              zzz

                              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.

                          • Re: Need a custom SQL report for discovery results
                            patriot

                            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?

                              • Re: Need a custom SQL report for discovery results
                                mharvey

                                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.

                                  • Re: Need a custom SQL report for discovery results
                                    patriot

                                    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.

                                      • Re: Need a custom SQL report for discovery results
                                        mharvey

                                        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)

                                          • Re: Need a custom SQL report for discovery results
                                            patriot

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

                                              • Re: Need a custom SQL report for discovery results
                                                mharvey

                                                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)

                                                  • Re: Need a custom SQL report for discovery results
                                                    patriot

                                                    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?

                                                      • Re: Need a custom SQL report for discovery results
                                                        mharvey

                                                        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.

                                                          • Re: Need a custom SQL report for discovery results
                                                            mharvey

                                                            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)

                                                              • Re: Need a custom SQL report for discovery results
                                                                patriot

                                                                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?

                                                                • Re: Need a custom SQL report for discovery results
                                                                  Jeff Catlin

                                                                  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.

                                          • Re: Need a custom SQL report for discovery results
                                            karltbraun

                                            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?

                                            • Re: Need a custom SQL report for discovery results
                                              Jörg

                                              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