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
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
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
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
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 *]
Thanks C.boggs
For that day OR just average for whole time range?
sorry I am calculating for last 7 days..so last 7 days count of a particular host by the total
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 *
Did you get a chance to test this query?
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
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
Ya correct I want the same thing like the table you showed with totals and the last row with avg
or.. do you just want the last row? - ie just the averages?
meh, formatting,, you get the point
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??
h1=(2334 +5580+9788 )/3 (which is number days h1 had data) ..This is what I am looking for
Would this give not you what you need?
index=abc sourcetype=xy.. |stats avg(host) by host
run over 7 days
actually thats total rubbish - it wont even work. (commenting on my phone)
yup it gives you two columns one with host names and the other avg(host) with no values
nope,I tried that earlier and it does not give you the _time field also
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)