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