18 Replies Latest reply on Aug 11, 2010 11:01 AM by lbyoung

    Report Writer - Nodes Down More than 3 Days

    lbyoung

      I am using Orion NPM to monitor several hundred sites that are managed by local administrators.  I would like to have a report produced that will list all devices that have been down for more than 3 days so we can contact the administrator to see if that device has been taken offline and should thus be removed.  I have been playing around with Report Writer to try and find this functionality but have not yet succeeded.  Any suggestions?

        • Re: Report Writer - Nodes Down More than 3 Days
          lbyoung

          A report showing "Last Uptime" for down nodes would also work if it could be sorted by date so those sites down the most amount of time appeared first.

          • Re: Report Writer - Nodes Down More than 3 Days
            mezdem

            Hi there, I am looking into something similar this avo.. I have come up with the below but it needs tweaking still..I only have one node down and its only gone down today...so you may need to come back to me if it does not look right.

            This selects all nodes that are currently down, (not UP anyway) and where alerts for node down were raised within as down for greater than 3 days.

             

             

             

             

             

             

             

            SELECT

             

             

            TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType,

            dbo

             

            .Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

            NodeID

            FROM

             

             

            dbo.ActionDefinitions INNER

            JOIN

            dbo

             

            .AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER

            JOIN

            dbo

             

            .AlertLog INNER

            JOIN

            dbo

             

            .Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.

            AlertDefID

            GROUP

             

             

            BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

            NodeID

            HAVING

             

             

            (dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3)

             

             Try this, It does not at this moment exclude any devices that have been flapping just those that have been down for 3 days..

              • Re: Report Writer - Nodes Down More than 3 Days
                lbyoung

                Thanks for the script, unfortunately it is not giving me any nodes.  I have 4-5 nodes that have been down for about a week and they are not showing up in the SQL Query.  I am not getting any error messages and unfortunately, my background is in Network Architecture so I have very limited SQL experience to edit the script.  Please keep me posted if you have any suggestions or updates and again, I thank you for your help.

                  • Re: Report Writer - Nodes Down More than 3 Days
                    lbyoung

                    It is possible that I am entering the script into the wrong location in Report Writer as this is my first attempt.  I am going through File-  New Report - Advanced SQL and then pasting the script under the SQL tab and running the Execute SQL Query function.

                      • Re: Report Writer - Nodes Down More than 3 Days
                        mezdem

                        no your doing it right - i have found the error in my code - will get back to you with it !

                          • Re: Report Writer - Nodes Down More than 3 Days
                            mezdem

                            SELECT

                             

                            TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType,

                            dbo

                            .Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.NodeID

                            FROM

                             

                            dbo.ActionDefinitions INNER JOIN

                            dbo

                            .AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER JOIN

                            dbo

                            .AlertLog INNER JOIN

                            dbo

                            .Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.AlertDefID

                            GROUP

                             

                            BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.NodeID

                            HAVING

                             

                            (dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3)

                             

                              • Re: Report Writer - Nodes Down More than 3 Days
                                lbyoung

                                That is giving me the nodes I am looking for!  Great job!  If I can ask one more small request, that would be great.  Is it possible to include the ability to sort the columns?  I would like to be able to sort by date?  If that is not possible, I understand and greatly appreciate your help with the script.  Thanks again!

                                  • Re: Report Writer - Nodes Down More than 3 Days
                                    lbyoung

                                    One of our SQL guys happened to be in the office and was able to get the date piece working.  We added the following line to the end of the code:

                                    order by AlertRaised desc

                                    Thank you so much for your help in getting this done.

                                      • Re: Report Writer - Nodes Down More than 3 Days
                                        lbyoung

                                        DANG IT!  I thought we had it but I just ran into a snag.  The report correctly lists the nodes that have been down for longer than 3 days but it also lists the devices that are currently down.  I ran the report a few minutes ago to test the "sort by date" function and one of the nodes that had just gone down 5 minutes ago showed in the report.  Not sure how to go about telling the report to not select devices that have gone down less than 3 days ago and not come back up yet.  Need something that says last response time more than 3 days ago maybe.  I will be more than happy to be your guinea pig to test scripts.  Again, thanks for all your hard work.

                                        • Re: Report Writer - Nodes Down More than 3 Days
                                          mezdem

                                          no worries glad to help, 10 years of being a DBA and Developer and newly a cisco network bod, I  still hanker to get involved back in the sql code  !! (sad i know)..

                                          It is also helping Me build a database structure in my mind - only had it installed a month!

                                            • Re: Report Writer - Nodes Down More than 3 Days
                                              mezdem

                                              Hmmmm i think that was the other part of the code i took out - i was trying to get it to only look for ones that were over 3 days.. I will need a 'NOT IN' clause again.. I will have a look...

                                              again its difficult if we have different data to work with!

                                                • Re: Report Writer - Nodes Down More than 3 Days
                                                  lbyoung

                                                  Appreciate the help.  I am the opposite.  I have 10+ years of Cisco experience but am just getting into the SQL realm because of Orion.

                                                  • Re: Report Writer - Nodes Down More than 3 Days
                                                    mezdem

                                                    i cannot test as i have no data like yours!

                                                    try this..

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                     

                                                    SELECT

                                                     

                                                     

                                                    TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType,

                                                    dbo

                                                     

                                                    .Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

                                                    NodeID

                                                    FROM

                                                     

                                                     

                                                    dbo.ActionDefinitions INNER

                                                    JOIN

                                                    dbo

                                                     

                                                    .AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER

                                                    JOIN

                                                    dbo

                                                     

                                                    .AlertLog INNER

                                                    JOIN

                                                    dbo

                                                     

                                                    .Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.

                                                    AlertDefID

                                                    GROUP

                                                     

                                                     

                                                    BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

                                                    NodeID

                                                    HAVING

                                                     

                                                     

                                                    (dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3

                                                    )

                                                    and

                                                     

                                                     

                                                    dbo.Nodes.nodeid NOT IN

                                                    (

                                                     

                                                     

                                                    select nodeid from dbo.

                                                    ResponseTime_Detail

                                                    where

                                                     

                                                     

                                                    DateTime > getdate()-3)

                                                     

                                                    order

                                                     

                                                     

                                                    by dateraised desc

                                                     

                                                      • Re: Report Writer - Nodes Down More than 3 Days
                                                        lbyoung

                                                        No nodes found with that one.  I have no problem testing

                                                          • Re: Report Writer - Nodes Down More than 3 Days
                                                            lbyoung

                                                            I opened a ticket with Solarwinds to see if there is a way to produce a "Last Seen" Field to show when the node last responded.  If so, a script could be written to say only show nodes with a "Last Seen" Field of greater than 72 hours 

                                                              • Re: Report Writer - Nodes Down More than 3 Days
                                                                mezdem

                                                                I have checked this morning, and for me the original script works fine, as the node that went down early this morning is not showing in the report - which is correct.

                                                                I am on version 10... are you the same?

                                                                  • Re: Report Writer - Nodes Down More than 3 Days
                                                                    mezdem

                                                                    and a slight change and this now works.. there is a table that records response times, this then reports on availabilty- so the last part of the query excludes all records that have been available 100 in last 3 days.. its getting close - see what solarwinds come back with!

                                                                     

                                                                    SELECT TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType,

                                                                    dbo

                                                                    .Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.NodeID

                                                                     

                                                                     

                                                                     

                                                                     

                                                                     

                                                                     

                                                                     

                                                                     

                                                                     

                                                                    FROM

                                                                     

                                                                     

                                                                    dbo.ActionDefinitions INNER

                                                                    JOIN

                                                                    dbo

                                                                     

                                                                    .AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER

                                                                    JOIN

                                                                    dbo

                                                                     

                                                                    .AlertLog INNER

                                                                    JOIN

                                                                    dbo

                                                                     

                                                                    .Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.

                                                                    AlertDefID

                                                                    GROUP

                                                                     

                                                                     

                                                                    BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

                                                                    NodeID

                                                                    HAVING

                                                                     

                                                                     

                                                                    (dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3

                                                                    )

                                                                    and

                                                                     

                                                                     

                                                                    dbo.Nodes.nodeid NOT IN

                                                                    (

                                                                     

                                                                     

                                                                    select nodeid from dbo.

                                                                    ResponseTime_Detail

                                                                    where

                                                                     

                                                                     

                                                                    DateTime > getdate()-3 and availability = 100

                                                                    )

                                                                    --order by dateraised desc

                                                                      • Re: Report Writer - Nodes Down More than 3 Days
                                                                        lbyoung

                                                                        Seems to work.  Its funny you mention that Report because I just got it mostly working on my end by modifying the Percent Down Last Month and making it Percent Down Last 72 Hours.   I then look at the top for the devices that are 100%.  I think either report will give me the info I need and I appreciate the work you put in to getting it working.  Thanks!!