reports - INterfaces and Nodes DownTime

Version 1

    hello Guys,

     

    I have here some SQL queries for  who need  generate a report that  contain the  interface or node down-time. I'm not data base expert but these examples have helped me a lot.

     

    Interfaces with problems (Other Status different of down)

     

    ===============================================

     

    select

     

    Nodes.StatusLED,

     

    Nodes.Caption as Node_Name,

     

    Nodes.NodeID as NodeID,

     

    Interfaces.StatusLED as Status_Interface,

     

    Interfaces.InterfaceIcon,

     

    Interfaces.Caption as Interfaces_Name,

     

    Interfaces.InterfaceID as InterfacesId,

     

    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

     

    INNER JOIN Interfaces

     

    ON Nodes.NodeID = INterfaces.NodeID

     

     

     

    INNER JOIN Events E ON

     

    E.NetworkNode = INterfaces.NodeID

     

    where (

     

    (Interfaces.Status <> '1') AND

     

    (Interfaces.Status <> '2') AND

     

    (Interfaces.Status <> '9')

     

    )

     

    group by

     

    Nodes.StatusLED,

     

    Nodes.Caption,

     

    Nodes.NodeID,

     

    Interfaces.InterfaceID,

     

    Interfaces.InterfaceIcon,

     

    Interfaces.Caption,

     

    Interfaces.StatusLED

     

    Interfaces Down

     

    =================================================

     

    select

     

    Nodes.Caption as Node_Name,

     

    Nodes.NodeID as NodeID,

     

    Interfaces.StatusLED as Status_Interface,

     

    Interfaces.InterfaceIcon,

     

    Interfaces.Caption as Interfaces_Name,

     

    Interfaces.InterfaceID as InterfaceId,

     

    Max(EventTime)as DownTime,

     

    Cast(DateDiff(day,Max(EventTime),

     

    getdate()) as varchar) + ' Day(s) ' + convert(char(8),

     

    dateadd(second,DateDiff(second,Max(EventTime),

     

    getdate()),0),14) as Duration

     

    From

     

    Events,

     

    Nodes INNER JOIN Interfaces ON Nodes.NodeID = INterfaces.NodeID

     

    where Interfaces.Status = 2

     

    group by

     

    Nodes.Caption,

     

    Nodes.NodeID,

     

    Interfaces.InterfaceID,

     

    Interfaces.Caption,

     

    Interfaces.StatusLED,

     

    Interfaces.InterfaceIcon

     

    Nodes Down

     

    ==========================================

     

    SELECT

     

    N.NodeID as NodeID,

     

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

     

    N.Caption

     

    Regards,

     

    Martrox.