Splunk Search

How to calculate the average of a field value for n number of days?

vrmandadi
Builder

I have the below query

index=abc sourcetype=xy.. |timechart span=1d count as events by host | addtotals

time           h1     h2        h3       h4             h5         h6       h7     total
2017-11-24  2334    68125   86384   120811         0         28020  0      305674
2017-11-25  5580    130912  172614  199817      0         38812 0      547735
2017-11-26  9788    308490  372618  4742120     0        112607   0    1277715

How do I calculate the average of h1 for the last 7 days and similarly for all other hosts

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=abc sourcetype=xy.. | bucket span=1d _time | stats count by _time host | stats avg(count) as count by host | eval temp=1 | xyseries temp host count

View solution in original post

somesoni2
Revered Legend

Give this a try

index=abc sourcetype=xy.. | bucket span=1d _time | stats count by _time host | stats avg(count) as count by host | eval temp=1 | xyseries temp host count

View solution in original post

vrmandadi
Builder

Thanks this one worked

index=abc sourcetype=xy.. | bucket span=1d _time | stats count by _time host | stats avg(count) as count by host | eval temp=1 | xyseries temp host count

Can you move it to answers,I will accept it

c_boggs
Explorer

Are you looking to calculate the average from daily counts, or from the sum of 7 days worth? This is the confusing part. If it's the former, are you looking to do this over time, i.e. see the average every 7 days, or just a single 7 day period? I'll tackle the first scenario - calculate the average count of events, per host, per day, over a period of 7 days.

In any case, timechart can't really do this in one step - so you'll need to bucket/bin the events first, then use a couple of stats commands.. This will group events by day, then create a count of events per host, per day. The second stats will then calculate the average daily count per host over whatever time period you search (the assumption is 7 days) The eval is just to round the average down to 2 decimal places.

yoursearchhere 
| bin span=1d _time
| stats count as dailycount by host, _time
| stats avg(dailycount) as average_count_per_day by host
| eval average_count_per_day=round(average_count_per_day,2)

Calculating this average every 7 days gets a bit more complicated - I believe eventstats would be a better solution in that case.

If you're looking for an average total events for 7 days, this would change a bit, and you would need to search for more than 7 days obviously - otherwise the total is the same as the average as
nickhillscpl mentioned.

yoursearchhere  
| bin span=7d _time 
| stats count as 7daycount by host, _time   
| stats avg(7daycount) as average_events_per_7days by host
| eval average_events_per_7days=round(average_events_per_7days,2)

Hope this helps.

EDIT: I see from the comments you are looking to retain the formatting you have in the table.. THis should do the trick:

   yoursearch
  | timechart span=1d count by host
  | addtotals
  | appendpipe [stats avg(*) as *]

vrmandadi
Builder

Thanks C.boggs

0 Karma

somesoni2
Revered Legend

For that day OR just average for whole time range?

0 Karma

vrmandadi
Builder

sorry I am calculating for last 7 days..so last 7 days count of a particular host by the total

0 Karma

somesoni2
Revered Legend

It's still not very clear. Could you post the expected output per your sample data in question. Give this a try as well.

index=abc sourcetype=xy.. |timechart span=1d count as events by host | addtotals
| stats avg(*) as *
0 Karma

somesoni2
Revered Legend

Did you get a chance to test this query?

0 Karma

vrmandadi
Builder

ok for example H1 has 25 count each for last 4 days out of 100 events each day.Then the average is calculated

100-total events of h1 for last 4 days
400-total events

average of h1=100/4 =25

0 Karma

nickhills
Ultra Champion

So you want a results table which would would look like this: ?

             h1     h2       h3    h4   total      
Day1    25      33       33     9      100
Day2    25      30       36     9      100
Day3    25      36       9     33      100
Day4    25      33       33     9      100
 AVG     25     33       27.5    15
If my comment helps, please give it a thumbs up!
0 Karma

vrmandadi
Builder

Ya correct I want the same thing like the table you showed with totals and the last row with avg

0 Karma

nickhills
Ultra Champion

or.. do you just want the last row? - ie just the averages?

If my comment helps, please give it a thumbs up!
0 Karma

nickhills
Ultra Champion

meh, formatting,, you get the point

If my comment helps, please give it a thumbs up!
0 Karma

somesoni2
Revered Legend

So, considering your sample data of

time h1 h2 h3 h4 h5 h6 h7 total
2017-11-24 2334 68125 86384 120811 0 28020 0 305674
2017-11-25 5580 130912 172614 199817 0 38812 0 547735
2017-11-26 9788 308490 372618 474212 0 112607 0 1277715

The output should be (single row, one column for each host)

h1=(2334 +5580+9788 )/3 (which is number days h1 had data) , OR
h1=(2334 +5580+9788 )/7 (which is the total number of hosts) , OR something else??

vrmandadi
Builder

h1=(2334 +5580+9788 )/3 (which is number days h1 had data) ..This is what I am looking for

0 Karma

nickhills
Ultra Champion

Would this give not you what you need?
index=abc sourcetype=xy.. |stats avg(host) by host
run over 7 days

If my comment helps, please give it a thumbs up!
0 Karma

nickhills
Ultra Champion

actually thats total rubbish - it wont even work. (commenting on my phone)

If my comment helps, please give it a thumbs up!
0 Karma

vrmandadi
Builder

yup it gives you two columns one with host names and the other avg(host) with no values

0 Karma

vrmandadi
Builder

nope,I tried that earlier and it does not give you the _time field also

0 Karma

nickhills
Ultra Champion

your search returns the total number of events of h1 on each of 7 days.
The 'average of h1 for that day' makes no sense (because it will be the same) as the total.

Do you mean the avg number of events for h1 in the last week?
(and h2, h3 etc)

If my comment helps, please give it a thumbs up!
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!