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'
)
)
)