14 Replies Latest reply on Jan 23, 2017 8:45 AM by rana468

    How long any node is down

    contracer

      Hi,

      I´m looking for a report to show me how long any node is down... (only nodes in down status now).

        • Re: How long any node is down
          rutgerht

          Contracer,

          I found this post to have useful info: Re: Include downtime in current node down report

          If you drop the following (slightly modified) code in an Advanced SQL report it should do the trick.

           

          SELECT

          N.StatusLED as Status,N.Caption AS Device,MAX(E.EventTime) AS DownTime,

          Cast(DateDiff(day,MAX(E.EventTime),getdate()) as varchar) + ' Day(s) ' + convert(char(8),dateadd(second,DateDiff(second,MAX(E.EventTime),getdate()),0),14) as Duration

          FROM

          Nodes N

          INNER JOIN Events E ON E.NetworkNode = N.NodeID

          where N.status = 2

          GROUP BY

          N.StatusLED,

          N.Caption

            • Re: How long any node is down
              contracer

              Thanks a lot !

              • Re: How long any node is down
                slidermike

                I see how you did this in an advanced sql search but how can I do this slightly modified?


                What I need to do is a monthly report of network "outages" for a certain group we will refer to as Vincent.

                It would be helpful to create a report (monthly) that will show me which "Vincent" cisco router went offline & for how long (duration) along with the date of the outage.

                 

                Would anyone offer to assit me in how this would be sql coded please?

                Thank you in advance.

                Mike

                    • Re: How long any node is down
                      slidermike

                      contracer,

                      thank you that looks really nice.

                       

                      Two things though if you have the time.


                      #1 This precanned sql report is showing last 30 days (not a big deal but would prefer a monthly I can run at the beginning of each new month).

                      #2 Would you be kind enough to show me an example of how I would tweak it to only show my "Vincent" routers instead of all managed devices?

                      It would be hugely helpful to be able to break this down by the business unit which can be characterized by router device name.

                      An example of one for the Vincent group would be usrtrvinXXXX

                       

                      Thank you

                        • Re: How long any node is down
                          contracer

                          I´ll try create this query tomorrow in my work.

                            • Re: How long any node is down
                              slidermike

                              Contracer,

                              Thank you for your help. I look forward to seeing your script!

                               

                              Cheers

                              Mike

                                • Re: How long any node is down
                                  contracer

                                  Try this query:

                                   

                                  SELECT

                                      Nodes.Caption,

                                      StartTime.EventTime,

                                     DATEDIFF(Mi, StartTime.EventTime,

                                      (SELECT TOP 1

                                          EventTime

                                          FROM Events AS Endtime

                                          WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

                                              AND EndTime.NetObjectType = 'N'

                                              AND EndTime.NetworkNode = StartTime.NetworkNode

                                          ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

                                  FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

                                  WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND

                                  eventtime between dateadd(month, -1, getdate()) and getdate() and nodes.caption like '%vinc%'

                                  ORDER BY Nodes.Caption ASC

                                    • Re: How long any node is down
                                      slidermike

                                      contracer,

                                      good morning and thank you for the code!

                                       

                                      It looks real good & works.

                                      I have a couple of questions if you are willing to indulge me.

                                      1) It is reporting on events this month as well as last month. Does this make it a "30 day" report or is there a way to tweak the code to do a "Last Month" report?

                                      2) Is there a way to add another search parameter that would mean "any" in this portion of the query and nodes.caption like '%vinc%'"

                                      My desire is to be able to do the above query & also have it pull back results for another node with a specific name like rtrnyny80.

                                      3) Lastly, how do I get this saved report to import or show in the webpage list of reports so I or any other administrator can run it from the webgui?

                                      A) I answered my own question on #3. Opening the folder where your reports are stored & pasting the custom report into this folder & it works.

                                       

                                      I figured out how to sort it by changing ORDER BY Nodes.Caption ASC to ORDER BY StartTime.EventTime ASC on the last line of the code. Now it sorts by oldest event to newest.

                                       

                                      Thank you very much for what you have given me. I can see a lot of use for this in monthly reporting.

                                        • Re: How long any node is down
                                          contracer

                                          Try this query (last month):

                                           

                                           

                                          SELECT

                                              Nodes.Caption,

                                              StartTime.EventTime,

                                             DATEDIFF(Mi, StartTime.EventTime,

                                              (SELECT TOP 1

                                                  EventTime

                                                  FROM Events AS Endtime

                                                  WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

                                                      AND EndTime.NetObjectType = 'N'

                                                      AND EndTime.NetworkNode = StartTime.NetworkNode

                                                  ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

                                          FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

                                          WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND (nodes.caption like '%vinc%' OR nodes.caption like '%rtrnyny80%') and

                                          eventtime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))


                                          --ORDER BY Nodes.Caption ASC

                                          ORDER BY StartTime.EventTime ASC

                                            • Re: How long any node is down
                                              slidermike

                                              contracer,

                                              Thank you VERY much.

                                              This works perfectly for every variable!

                                               

                                              I really appreciate your time & expertise.

                                               

                                              I will use this template for several reports.

                                              Have a great week!

                                               

                                              Mike

                                              • Re: How long any node is down
                                                slidermike

                                                contracer,

                                                good morning.

                                                 

                                                I have one more request regarding this report if you dont mind.

                                                The report ran as expected & I have the report writer automatically generate & email it to me.

                                                The next situation is the boss, while impressed with the pdf would prefer a csv formated version of the report so he can manipulate the data as necessary.

                                                I have looked in the report writer but it only offers the choice of sending the report in html or pfd.

                                                Any suggestions on how to get this emailed out monthly in csv?

                                                 

                                                Again thank you for all your efforts!

                                                Mike

                                                *****update*****

                                                I found this helpful link.

                                                It allows the link to be emailed for the csv.

                                                http://thwack.solarwinds.com/docs/DOC-169128

                                                I am onto opening a case with solarwinds because when I run the report with the csv I get the cached version of the report from the previous month. Works fine running it as pdf or as a webpage but exporting to csv flakes out.

                                                • Re: How long any node is down
                                                  slidermike

                                                  Hello,

                                                  I have a question about an existing report I have.

                                                  Currently the report runs based upon the current date & subtracts 1 to get the last months date range.

                                                  It turns out my boss is NOT running the report on the 1st of each month as I told him he needed to for the results to be the previous months values.

                                                  As a result I now have to ask for your help in getting results filtered by each month and not just the last month from when I execute the report.

                                                   

                                                  Here is how my current report looks in sql.

                                                  SELECT

                                                      Nodes.Caption,

                                                      StartTime.EventTime,

                                                     DATEDIFF(Mi, StartTime.EventTime,

                                                      (SELECT TOP 1

                                                          EventTime

                                                          FROM Events AS Endtime

                                                          WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

                                                              AND EndTime.NetObjectType = 'N'

                                                              AND EndTime.NetworkNode = StartTime.NetworkNode

                                                          ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

                                                  FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

                                                  WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND (nodes.caption like '%USRTAVIN%' OR nodes.caption like '%USRTRNYNY80%' OR nodes.caption like '%USRTRLACA001%') and

                                                  eventtime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))


                                                  --ORDER BY Nodes.Caption ASC

                                                  ORDER BY StartTime.EventTime ASC

                                • Re: How long any node is down
                                  rana468

                                  How to get the report for particular custom group. Above SQL query include node caption only. For example I have created custom group xyz, abc etc. Now I want outage report for XYZ custom group.Would you please let me know how to add custom group in above query.