I have a successful query to report when a tunnel goes above a certain bandwidth percentage, but my issue is it reports on every poll... so if a site is above that threshold for 8 hours straight, I'll get 100's of hits. Any SQL gurus that can figure a way to summarize per hour or minimize the number of reported polls? Below is my query:
SELECT
nodes.Caption
,Interfaces.InterfaceName
,InterfaceTraffic.DateTime
,InterfaceTraffic.In_Maxbps
,Interfaces.InBandwidth
,CAST (100 * (InterfaceTraffic.In_Maxbps) / (Interfaces.Inbandwidth) AS nvarchar)+ ' %' AS Max_Bps_Pct
FROM dbo.Interfaces
INNER JOIN dbo.nodes ON nodes.nodeID=interfaces.nodeID
INNER JOIN dbo.InterfaceTraffic ON nodes.nodeID=InterfaceTraffic.nodeID
WHERE
interfaces.InterfaceType = '135'
AND nodes.MachineType LIKE '%AdTran%'
AND (InterfaceTraffic.In_Maxbps) / (Interfaces.Inbandwidth) * 100 > '70'
AND nodes.Caption LIKE '%pmc-pho%'
AND DateTime BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
ORDER BY DateTime DESC
