1 Reply Latest reply on Dec 7, 2015 11:26 AM by deltabravo

    Time of Day minimum threshold

    deltabravo

      Any one know if this is possible?  Maybe an advanced sql alert.

       

      Example is Internet Uplink interfaces.  (Hope I'm articulating this well enough) Basically predictive in nature.  I know that today at 2:00 PM the traffic should be at x, based on the pattern over two weeks.  If the traffic pattern falls outside one or two standard deviations of this average, alert.

        • Re: Time of Day minimum threshold
          deltabravo

          Here's what I developed so far.  I'm in no way a sql guru, so would welcome any feedback.  This would be a sql based alert that basically would alert if bandwidth fell below x number of standard deviations from normal for that hour of day and day of week.  Threshold still needs to be done.

           

           

          select

          Interfaces.InterfaceID,

          Interfaces.FullName

          from

          Interfaces

          join (

          select

          stats.InterfaceID,

          stats.FullName,

          Interfaces.Inbps,

          stats.Inputbps,

          stats.StDevInputbps

          from

          (

          SELECT

          ifaces.InterfaceID,

          ifaces.FullName,

          DatePart(weekday, i.DateTime) as DOW,

          DatePart(hour, i.DateTime) as HOD,

          avg(i.In_Averagebps) as Inputbps,

          avg(i.in_minbps) as inMinbps,

          stdev(i.In_Averagebps) as StDevInputbps,

          2 * stdev(i.In_Averagebps) as twoStDevInputbps,

          3 * stdev(i.In_Averagebps) as threeStDevInputbps

          FROM

          [dbo].[InterfaceTraffic_Hourly] i

          join [dbo].[Interfaces] ifaces on ifaces.InterfaceID = i.InterfaceID

          where

          DatePart(weekday, DateTime) = DatePart(

          weekday,

          GETDATE()

          )

          and DatePart(hour, DateTime) = DatePart(

          hour,

          GETDATE()

          )

          group by

          ifaces.InterfaceID,

          ifaces.FullName,

          DatePart(weekday, DateTime),

          DatePart(hour, DateTime)

          ) stats

          join Interfaces on Interfaces.InterfaceID = stats.InterfaceID

          where

          Interfaces.Inbps < (

          stats.Inputbps - stats.StDevInputbps

          )

          ) outerStats on outerStats.InterfaceId = Interfaces.InterfaceID

          where

          Interfaces.InternetUplink = 1