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