I'm having some difficulty putting together a query that will pull down 30 minute average response times for each of my nodes. I would like the average to be calculated as a moving average. The window size for the moving average would be 5 minutes.
The problem I'm running into is the ResponseTime.DateTime column. NPM stores the response time datetime column as the day, hour, minute and second NPM got the response back. I don't know how to count time up to 5 minutes and average those records, and then for all of the 5 min avgs I have, average them up to 30 minute intervals. And then spit them into a report.
My polling interval is set to 60 seconds, so I have a record of response time every minute (except for the first month of my data when I had it set to 120 seconds).
The pseudo code would be something like:
foreach node {
select responseTime.dateTime as DateTime
select responseTime.NodeID
select responseTime.AvgResponseTime
for every 5 minutes of records selected {
FiveMinAvg = avg(responseTime.AvgResponseTime)
for every 30 minutes of records selected {
ThirtyMinRolling = avg(x)
}
}
}
<do some time operation to spit the DateTime column into 30 minute intervals>
group stuff
Any report wizards have suggestions? Thanks for you time.
EDIT: I don't need a moving average. I just need the 30 minute average. I realized it doesn't change much in terms of smoothing.