1 Reply Latest reply on Dec 24, 2015 11:28 AM by khanwasimit

    Node/Interface down,up and total down time script for only today's down events

    @thwack09

      I have created below script for the node which goes down and up and there total down time in hour:minute format, can able to take last 12hrs of data. I have a requirement to take the report of only today's down event that is only 24hrs data. It should take the the data of todays only whenever I run the script. And I want switch type FX should be considered if only its down time is greater than 10min. apart from this all switch type should be included in report.

       

      /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

      select

      Events.EventTime,

      Nodes.StatusLED as Status,

      Nodes.Branch,

      Nodes.Caption,

      Nodes. IP_Address,

      Nodes.Switch_Type,

      (Select TOP 1

      EventTime

      From Events AS Endtime

      where EndTime.EventTime > Events.EventTime AND EndTime.EventType = 5

      AND EndTime.NetObjectType = 'N'

      AND EndTime.NetworkNode = Events.NetworkNode

      Order by EndTime.EventTime) AS Uptime,

       

       

      Cast(DateDiff(minute,Events.EventTime,(Select TOP 1 EventTime

      From Events AS Endtime

      where EndTime.EventTime > Events.EventTime AND EndTime.EventType = 5

      AND EndTime.NetObjectType = 'N'

      AND EndTime.NetworkNode = Events.NetworkNode

      Order by EndTime.EventTime))  / 60  as varchar(10)) + ':' +  Cast(DateDiff(minute,Events.EventTime,(Select TOP 1 EventTime

      From Events AS Endtime

      where EndTime.EventTime > Events.EventTime AND EndTime.EventType = 5

      AND EndTime.NetObjectType = 'N'

      AND EndTime.NetworkNode = Events.NetworkNode

      Order by EndTime.EventTime))  % 60 as varchar(10) ) as 'HH:MM'

       

       

      From Events

      INNER JOIN Nodes

      ON Events.NetworkNode = Nodes.NodeID

      where (Switch_Type IN ('FX','AX','BX','ADX','MCR','DX')) AND(Events.EventType =1) AND (Events.NetObjectType = 'N' AND

       

       

      eventtime between dateadd(Hour, -12, getdate()) and getdate())

      Order by

      Events.EventTime ASC

       

      ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

       

       

       

       

      Similar with interface down, up and total down time, but I am facing same issue here...is there any way I can extract only todays data rather than last 12hrs.

      **********************************************************************************************

      select

      Events.EventTime,

      Interfaces.StatusLED AS Interface_Status_Icon,

      Nodes.Branch,

      Nodes.Caption,

      Nodes.Switch_Type,

      Interfaces.Caption AS Interface_Caption,

      (Select TOP 1

      EventTime

      From Events AS Endtime

      where EndTime.EventTime > Events.EventTime AND EndTime.EventType = 10

      AND EndTime.NetObjectType = 'I'

      AND EndTime.NetworkNode = Events.NetworkNode

      Order by EndTime.EventTime) AS Uptime

      From Events

      INNER JOIN Nodes

      ON Events.NetworkNode = Nodes.NodeID

      INNER JOIN Interfaces

      ON Interfaces.NodeID =  Events.NetworkNode

       

       

      where (Switch_Type IN ('FX','AX','BX','ADX','MCR','DX')) AND(Events.EventType = 10 AND (Interfaces.Caption Like '%INPUT%' AND Events.Message Like '%INPUT%'))

      AND (eventtime between dateadd(Hour, -6, getdate()) and getdate())

      Order by

      Events.EventTime ASC

       

      *****************************************************************************************************