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.

utilization greater than 45% count - sql

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'

    )

  )

)

RH-Customer_WAN_gt_45_COUNT.OrionReport
  • I do not see a time/date range, how is it that this is for one month?
  • I like the 5 minute threshold for triggering but I do have 2 questions.

    #1 How are you setting the time/date?

    #2 How are you telling the report to only return hits that exceed the given threshold for 5 minutes or more?

  • great questions...this is old so I'll have to reveiw...give me a few.
    apparently I was out to lunch (far away) when commented..sorry about that.

  • Ah yes...

    #1, kind of cheating...My retention period for details is 7 days.  hence, no date info...I just query the whole [InterfaceTraffic_Detail] table.

    #2, my settings are I pull stats every 5 minutes; my understanding is the stats give me min/max/avg for those 5 minutes.

        regardless of which system (node or SW chews avg), I'm selecting/calculating based on the columns for a poll - InterfaceTraffic_Detail.In_Averagebps  and out_

        each row in the table *should* represent a 5 minute poll cycle.

       the way I count the number of hits is by the last select arg...

             COUNT(*)

                     This is why I have to have everything else in the group by arg.

    Let me know if you have further questions.

  • Thank you for the quick response njoylif.

    For those of us who data retention longer than 7 days & yet are sql challenged, would you mind giving an example of what the command would look like to force the date to be the "previous month" (not last 30 days but rather the last calendar month)?

    I think I can use almost everything else you created (including the 5 minute threshold) but I retain data for 60 or more days and would like to limit the responses to the previous month.

    Thank you

    Mike

  • njoylif, thanks again for your efforts.

    Now I am off to figure out where to put the date/time code you graciously added at the bottom as a note.

    I appreciate your efforts!

    Mike

  • NP.  it would go in your where clause...you'd tweak the specifics to meet your needs of the interfaces/nodes...i.e.

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

    AND    (   LTRIM(MONTH(t.DateTime)) = (LTRIM(MONTH(getdate())) -1)  -- last month ) -- ADD HERE - before last ')' of where clause

    )

    GROUP BY

  • njoy, since you have been willing to assist (and I am a newb at sql) I would like to show you how I have modified what you originally posted (pre-date modifications).

    What I am posting here works but again without date ranges it is probably reaching 60+ days into the db.

    Would you be kind enough to show me how you might inject the time/date code into what I have?

    So far, even with your expert advice I am coming up with sql errors.

    FROM
    (InterfaceTraffic_Detail INNER JOIN Interfaces ON (Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID) )
    INNER JOIN Nodes ON (Nodes.NodeID = Interfaces.NodeID)

    WHERE
    (Nodes.caption LIKE '%CARTR%' OR Nodes.caption LIKE '%USRTR%') AND (Nodes.caption NOT LIKE '%.Internet%' OR Nodes.caption LIKE '%USRT2NYNY1441%') AND (InterfaceName like '%') AND
    (   (InterfaceName LIKE '%Ser%' OR InterfaceName LIKE '%Gig%') AND (InterfaceName NOT LIKE '%.%')  )     AND
    (
        (((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) > 89)
             OR
        (((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 89)
    )

    GROUP BY
    Nodes.caption, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,
    Interfaces.InBandwidth, Interfaces.Caption

    ORDER BY Nodes.caption

  • whew....try this...I think the parens were off

    WHERE

    (

      (Nodes.caption LIKE '%CARTR%' OR Nodes.caption LIKE '%USRTR%')

      AND

      (Nodes.caption NOT LIKE '%.Internet%' OR Nodes.caption LIKE '%USRT2NYNY1441%')

    AND

      (

        (InterfaceName LIKE '%Ser%' OR InterfaceName LIKE '%Gig%')

        AND

        (InterfaceName NOT LIKE '%.%')

      )    

    AND

      (

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

             OR

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

      )

    AND   

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

    )

    GROUP BY

    Nodes.caption, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,

    Interfaces.InBandwidth, Interfaces.Caption

    ORDER BY Nodes.caption

  • I get this error when I copy/paste it.sql_error.jpg