4 Replies Latest reply: Jun 22, 2012 3:50 AM by Shane Spencer RSS

    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?

        • Re: Average aggregation of data from 5 minute to 1hour
          Stefan Wühl

          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.