average of a sum of values based on differing values in a different column

[stephen123]

Hi, given the data below, I want to find the average sum of a1 to a3 and b1 to b3 every 10 minutes

time field1 field2

10:01 a-1 10

10:02 a-2 10

10:02 a-3 10

10:04 b-1 20

10:05 b-2 20

10:05 b-3 20

10:01 a-1 20

10:02 a-2 20

10:02 a-3 20

10:04 b-1 40

10:05 b-2 40

10:05 b-3 40

.

.

.

10:11 a-1 10

10:12 a-2 10

10:12 a-3 10

10:14 a-1 20

10:15 a-2 20

10:15 a-3 20

results would be

10:00 a 45 ((10 X 3)+(20 X 3))/2

10:00 b 90 ((20 X 3)+(40 X 3))/2

10:10 a 45 ((10 X 3)+(20 X 3))/2

etc

Thanks

[lguinn2 · Accepted solution]

Not sure exactly what that means. But, I assume that you have two fields: first, code which is the "a-1", "b-1", etc.
The second field I will call counter. I don't understand why you divide by 2 - for an average you should divide by the number of occurrences.

yoursearchhere
| rex field=code "(?<category>.)"
| stats avg(number) by category

Although this might do what you want

yoursearchhere
| rex field=code "(?<category>.)"
| stats sum(number) as subtotal count by category code
| stats sum(subtotal) as total sum(count) as count dc(code) as numCodes by category
| eval Result = total / (count/numCodes)

The above assumes that you will run the search every 10 minutes. But if you were planning to run the search over a longer time period (like the last 24 hours) and report out the 10-minute blocks within the range, do this:

yoursearchhere
| rex field=code "(?<category>.)"
| bucket _time span=10m
| stats sum(number) as subtotal count by category code _time
| stats sum(subtotal) as total sum(count) as count dc(code) as numCodes by category _time
| eval Result = total / (count/numCodes)
| eval Time=strftime(_time,"%x %H:%M")
| table Time Result