Splunk Search

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

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

Tags (3)
1 Solution
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
``````
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
``````
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

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
| appendpipe [stats avg(*) as *]
``````
Builder

Thanks C.boggs

Revered Legend

For that day OR just average for whole time range?

Builder

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

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 *
``````
Revered Legend

Did you get a chance to test this query?

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

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

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

Ultra Champion

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

If my comment helps, please give it a thumbs up!
Ultra Champion

meh, formatting,, you get the point

If my comment helps, please give it a thumbs up!
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??

Builder

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

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!
Ultra Champion

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

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

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

Builder

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

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!
.conf21 Now Fully Virtual!