6 Replies Latest reply on Oct 26, 2012 8:36 AM by PavelSrot

    how to determine downtime of a cisco node

    supportadmin

      Hello,

       

      I have a setup wherein I have around 100 remote sites. These remote sites are connected to the central sites through VSAT. There is constant power failure at these remote sites. I have Cisco switch installed at each of these remote sites. Whenever, there is a power failure, I get an snmp trap from the Cisco switch stating that the switch had rebooted. Using this I want to calculate the time for which the site was down due to power failure. Is this possible?

       

      One way of doing it is to do it manually. I mean I check the down time event (event ID 1) and the cisco node reboot ID (event ID14). The difference between these two events would give me the down time. How do I find this for more than one node? I want to automate this process. Please help...

        • Re: how to determine downtime of a cisco node
          PavelSrot

          Here is quick example what you can enter into ReportWriter as Advanced SQL.

          SELECT EventsTime.NetObjectID,N.Caption, DATEDIFF(hh, EventsTime.DownTime, EventsTime.UpTime) AS [Hours], DATEDIFF(mi, EventsTime.DownTime, EventsTime.UpTime) % 60 AS [Minutes] FROM (

          SELECT E.NetObjectID,E.EventTime AS DownTime,

          (SELECT TOP 1 E2.[EventTime] FROM [dbo].[Events] E2 WHERE E2.[NetObjectID]=E.[NetObjectID]

          AND E2.EventTime>E.EventTime

          AND E2.[EventType]=2

          ) AS UpTime

           

          FROM [dbo].[Events] E

          WHERE E.[EventType]=1 -- down

          ) AS EventsTime

          INNER JOIN [dbo].[Nodes] AS N ON N.NodeID=EventsTime.NetObjectID

          WHERE UpTime IS NOT NULL

          AND DownTime IS NOT NULL

            • Re: how to determine downtime of a cisco node
              supportadmin

              Hello,

              Thank you for replying. I tried this report. Unfortunately, when I execute query, I see no result. It is blank. please help...

                • Re: how to determine downtime of a cisco node
                  PavelSrot

                  Can you try first execute this query:

                  SELECT TOP 1 E2.[NetObjectID], E2.[EventTime] FROM [dbo].[Events] E2 WHERE E2.[EventType]=2 -- up

                   

                  and then

                  SELECT TOP 1 E2.[NetObjectID], E2.[EventTime] FROM [dbo].[Events] E2 WHERE E2.[EventType]=1 -- down

                   

                  and try to find out whether if they have minimally one common NetObjectID? And if so, wherther time in the first query of common NetObjectID is greater than time in the second result with common NetObjectID?

                    • Re: how to determine downtime of a cisco node
                      supportadmin

                      Hello,

                       

                      Sorry for the delayed reply.

                      When I execute the first query, I get no result.

                       

                      When I execute the second query I get one result wherein it says NetObjectID1 and Event time for that is 28-Sep-12 09:17 AM.

                       

                      Thank you in advance..

                        • Re: how to determine downtime of a cisco node
                          PavelSrot

                          Here is little modification in my previous query. This should now works

                          SELECT EventsTime.NetObjectID,N.Caption, DATEDIFF(hh, EventsTime.DownTime, EventsTime.UpTime) AS [Hours], DATEDIFF(mi, EventsTime.DownTime, EventsTime.UpTime) % 60 AS [Minutes] FROM (

                          SELECT E.NetObjectID,E.EventTime AS DownTime,

                          (SELECT TOP 1 E2.[EventTime] FROM [dbo].[Events] E2 WHERE E2.[NetObjectID]=E.[NetObjectID]

                          AND E2.EventTime>E.EventTime

                          AND (E2.[EventType]=5 OR E2.[EventType]=9) ORDER BY E2.[EventTime] DESC

                          ) AS UpTime


                          FROM [dbo].[Events] E

                          WHERE E.[EventType]=1 -- down

                          ) AS EventsTime

                          INNER JOIN [dbo].[Nodes] AS N ON N.NodeID=EventsTime.NetObjectID

                          WHERE UpTime IS NOT NULL

                          AND DownTime IS NOT NULL

                  • Re: how to determine downtime of a cisco node
                    mgibson

                    Here is what I use, Please be aware that there are custom fields in my code

                    SELECT

                        StartTime.EventTime,

                        Nodes.Caption,

                        Nodes.department,

                        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

                    month(eventtime) between (month(getdate()) - 2) and (month(getdate()) -1)

                    AND nodes.support = 'l'

                    ORDER BY Nodes.Caption ASC

                     

                    Also thought this would help, this is code for Rolling Device Outage Duration

                    SELECT NodeCaption,

                    SUM(CASE WHEN month(EVTDate) = 1 THEN downtime ELSE 0 END) AS Jan,

                    SUM(CASE WHEN month(EVTDate) = 2 THEN downtime ELSE 0 END) AS Feb,

                    SUM(CASE WHEN month(EVTDate) = 3 THEN downtime ELSE 0 END) AS Mar,

                    SUM(CASE WHEN month(EVTDate) = 4 THEN downtime ELSE 0 END) AS Apr,

                    SUM(CASE WHEN month(EVTDate) = 5 THEN downtime ELSE 0 END) AS May,

                    SUM(CASE WHEN month(EVTDate) = 6 THEN downtime ELSE 0 END) AS Jun,

                    SUM(CASE WHEN month(EVTDate) = 7 THEN downtime ELSE 0 END) AS Jul,

                    SUM(CASE WHEN month(EVTDate) = 8 THEN downtime ELSE 0 END) AS Aug,

                    SUM(CASE WHEN month(EVTDate) = 9 THEN downtime ELSE 0 END) AS Sep,

                    SUM(CASE WHEN month(EVTDate) = 10 THEN downtime ELSE 0 END) AS Oct,

                    SUM(CASE WHEN month(EVTDate) = 11 THEN downtime ELSE 0 END) AS Nov,

                    SUM(CASE WHEN month(EVTDate) = 12 THEN downtime ELSE 0 END) AS Dec,

                    '',

                    SUM(CASE WHEN month(EVTDate)>0 THEN downtime ELSE 0 END) AS Total

                    From (SELECT Nodes.Caption As NodeCaption,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 DownTime

                    ,StartTime.EventTime as EVTDate

                     

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

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

                      AND eventtime between dateadd(month, -12, getdate()) and getdate()) dt

                    Group By NodeCaption

                     

                    Here is Outage duration Last Month for LAN Devices

                    SELECT

                        StartTime.EventTime,

                        Nodes.Caption,

                        Nodes.Location,

                    nodes.nodeid,

                        StartTime.Message,

                        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.support = 'l'

                    ORDER BY Nodes.Caption ASC