This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

how to determine downtime of a cisco node

supportadmin over 11 years ago

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

0 PavelSrot over 11 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 supportadmin over 11 years ago in reply to PavelSrot

Hello,
Thank you for replying. I tried this report. Unfortunately, when I execute query, I see no result. It is blank. please help...
Cancel
Vote Up 0 Vote Down

Cancel
0 PavelSrot over 11 years ago in reply to supportadmin

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?
Cancel
Vote Up 0 Vote Down

Cancel
0 supportadmin over 11 years ago in reply to PavelSrot

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..
Cancel
Vote Up 0 Vote Down

Cancel
0 mgibson over 11 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 PavelSrot over 11 years ago in reply to supportadmin

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
Cancel
Vote Up 0 Vote Down

Cancel