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).
Interfaces.Caption as ICaption,
COUNT(*) as Count
(InterfaceTraffic_Detail INNER JOIN Interfaces ON (Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID) )
INNER JOIN Nodes ON (Nodes.NodeID = Interfaces.NodeID)
(Nodes.Caption LIKE 'R-XRT-%') AND
( (InterfaceName LIKE 'Ser%.%') AND (InterfaceName NOT LIKE 'Ser%.16') ) AND
(((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) > 45)
(((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 45)
Nodes.Caption, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,
ORDER BY Nodes.Caption
This will give you last month, weekdays, between 9-5
LTRIM(MONTH(t.DateTime)) = (LTRIM(MONTH(getdate())) -1) -- last month
DATEPART(dw, t.DateTime) between 2 and 6 -- weekdays
t.DateTime BETWEEN -- between 9 and 5
CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 09:00'
CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 17:00'