Average aggregation of data from 5 minute to 1hour

[shane_spencer]

I've been collecting Windows Perfmon data at a five minute interval for a large number of servers I manage. I've Processed this data in to QVD's that I load in to a Report - see attached.

For recent months data this is 5 minute interval what I want, but older data drilling down to the nearest 5 minutes is not required instead the average hourly would be sufficient. So to keep down the amount of data I'm having to load each day and to keep the size of the Report from getting too large I'd like to process these QVD's (for the older months) to average up the Values for the hour and save them to a new QVD (that is smaller in disk size).

I suspect it is fairly simple to do but I don't understand how. Can anyone help?

[swuehl · Accepted solution]

I think you just need to do a group by load using your relevant fields and then average the value, right?

The relevant fields are maybe all except value, Minute, and your date timestamps, but maybe you could remove some more fields that are not needed or just storing redundant information (file, file_name).

AVG:

LOAD

file, file_name, dir, Server, Year, Month, Monthyear, Date, Hour, Main_metric, sub_Metric, Metric,

avg(value) as AvgValue

Resident INPUT group by file, file_name, dir, Server, Year, Month, Monthyear, Date, Hour, Main_metric, sub_Metric, Metric;

where INPUT is your table loaded from qvd.

[shane_spencer]

Thank you! That seems to have reduced over 8000K to 300K so is going to be great!

AVG:
LOAD
Server, Year, Month, Monthyear, Date, Day, Hour, Main_metric, sub_Metric,
avg(value) as AvgValue
FROM *_Perfmon_$(prevmonth).qvd (QVD) group by Server, Year, Month, Monthyear, Date, Day, Hour, Main_metric, sub_Metric;

As per your suggestion I also got rid of a lot of unwanted data, the work I am doing was started by a colleague then handed over to me and there seems to be a lot of Fields that are probably not wanted so I can probably reduce the size of the other original QVD's by removing the redundant data.

One question is it possible to combine the WHERE command with the LOAD? This would allow me to get rid of the Nulll values in that field?

Where Len(Server)>0;

I do ommit them from the final load in to my Report/App but it would be better to do it here so I'm not storing rubbish data.

[swuehl]

Should be possible, just enter the where clause right before the group by:

FROM *_Perfmon_$(prevmonth).qvd (QVD) where Len(Server)>0 group by Server, Year, Month, Monthyear, Date, Day, Hour, Main_metric, sub_Metric

;

[shane_spencer]

Thanx! It's just a case of knowing where to put it.