Would this be a daily report, or are you aiming for different timeframes?
The Idea would be the last 30 days (also because after that threshold statistic is aggregated to the whole day in our current polling settings).
So the data I would like to have should be the average between samples collected in the "working hours" time frame each day, aggregated again making an average in the last 30 days.
Okay. I don't know how to accomplish that either! With any luck, you'll get your method from one of the many skilled forum members around. Following this so I can find the method as well.
I think I've solved this.
I use a 'timezone' custom property on nodes (it's an nvarchar populated like -1, -2, 4) etc to indicate the offset of the node time from gmt.
Query with just few details:
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Receive_Percent_Utilization,
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Transmit_Percent_Utilization
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
( DateTime BETWEEN dateadd(dd,-30,getdate()) AND getdate() )
(Datepart(HOUR,(DATEADD(hour,(DatePart(HOUR, DateTime)- cast(Time_Zone as float)) ,'20130215')) ) >= 7) AND
(Datepart(HOUR,(DATEADD(hour,(DatePart(HOUR, DateTime)- cast(Time_Zone as float)) ,'20130215')) ) <= 18)
Group by Nodes.Caption,Interfaces.InterfaceName
The bold part is where I use the 'Time_Zone' to modify the hour value, since the offset may cause the day to change, I have to convert it to a date (thus including that 20130215, you can have any date you want, maybe not 19000101) calculate the offset and then take only the hour part from it, and confronting it with the working hour I want.
Hope this may help.
Hello all. Ive been trying to do something like that but slightly easier, I think. I just need 1 timezone - but I only want the avg function to use 8-5. I don't want to plug in a static date of course. and I don't know sql, but in pseudo code, what I want is:
take from NODES, interfaces x, y and z, average the in and out traffic, but only between 0800 and 1700!