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
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
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