utilization greater than 45% count - sql

Version 4

    UPDATED:

    removed custom properties.  (got rid of hostname and serviceftp) for general consumption...

    See below SQL for datetime example for last calendar month.

    NOTE:  This pulls ALL of the info from details table and the time range is thus dependent on your retention settings for details on interfaces.

     

    this SQL takes serial interfaces (in my case) for wan connectivity and generates a report that counts the number of times utilization went gt (greater than) 45% for a five minute period (for the amount of time you keep detailed stats)

    I used this in a report that gets emailed out. (see attached for report, but you will have to tweak interfaces/% as desired).

    SELECT

    Nodes.NodeID,

    Nodes.Caption,

    Interfaces.InterfaceID,

    Interfaces.Caption as ICaption,

    Interfaces.OutBandwidth,

    Interfaces.InBandwidth,

    COUNT(*) as Count

     

    FROM

    (InterfaceTraffic_Detail INNER JOIN Interfaces ON (Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID) )

    INNER JOIN Nodes ON (Nodes.NodeID = Interfaces.NodeID)

     

    WHERE

    (Nodes.Caption LIKE 'R-XRT-%')  AND

    (  (InterfaceName LIKE 'Ser%.%') AND (InterfaceName NOT LIKE 'Ser%.16')  )    AND

    (

        (((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) > 45)

            OR

        (((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 45)

    )

     

    GROUP BY

    Nodes.Caption, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,

    Interfaces.InBandwidth, Interfaces.Caption

     

    ORDER BY Nodes.Caption

     

    NOTE:

    DATE/TIME Examples:

      This will give you last month, weekdays, between 9-5

    AND

      (

      LTRIM(MONTH(t.DateTime)) = (LTRIM(MONTH(getdate())) -1)  -- last month

      AND

      DATEPART(dw, t.DateTime) between 2 and 6  -- weekdays

      AND

        (

            t.DateTime BETWEEN  -- between 9 and 5

                CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 09:00'

                AND

                CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 17:00'

        )

      )

    )