https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317573#M94993 <P>So, considering your sample data of </P> <PRE><CODE>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 </CODE></PRE> <P>The output should be (single row, one column for each host)</P> <P>h1=(2334 +5580+9788 )/3 (which is number days h1 had data) , OR<BR /> h1=(2334 +5580+9788 )/7 (which is the total number of hosts) , OR something else?? </P> Fri, 01 Dec 2017 16:36:29 GMT somesoni2 2017-12-01T16:36:29Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317566#M94986 <P>I have the below query</P> <P>index=abc sourcetype=xy.. |timechart span=1d count as events by host | addtotals</P> <PRE><CODE>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 </CODE></PRE> <P>How do I calculate the average of h1 for the last 7 days and similarly for all other hosts </P> Fri, 01 Dec 2017 15:51:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317566#M94986 vrmandadi 2017-12-01T15:51:35Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317567#M94987 <P>For that day OR just average for whole time range?</P> Fri, 01 Dec 2017 15:57:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317567#M94987 somesoni2 2017-12-01T15:57:45Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317568#M94988 <P>sorry I am calculating for last 7 days..so last 7 days count of a particular host by the total</P> Fri, 01 Dec 2017 16:03:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317568#M94988 vrmandadi 2017-12-01T16:03:43Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317569#M94989 <P>your search returns the total number of events of h1 on each of 7 days.<BR /> The 'average of h1 for that day' makes no sense (because it will be the same) as the total.</P> <P>Do you mean the avg number of events for h1 in the last week?<BR /> (and h2, h3 etc)</P> Fri, 01 Dec 2017 16:22:02 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317569#M94989 nickhills 2017-12-01T16:22:02Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317570#M94990 <P>It's still not very clear. Could you post the expected output per your sample data in question. Give this a try as well.</P> <PRE><CODE>index=abc sourcetype=xy.. |timechart span=1d count as events by host | addtotals | stats avg(*) as * </CODE></PRE> Fri, 01 Dec 2017 16:25:10 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317570#M94990 somesoni2 2017-12-01T16:25:10Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317571#M94991 <P>I am trying to find out the average of each host</P> <P>average=count of h1 for 7 days /total count of all hosts and similarly for all hosts</P> Fri, 01 Dec 2017 16:25:34 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317571#M94991 vrmandadi 2017-12-01T16:25:34Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317572#M94992 <P>ok for example H1 has 25 count each for last 4 days out of 100 events each day.Then the average is calculated </P> <P>100-total events of h1 for last 4 days<BR /> 400-total events </P> <P>average of h1=100/4 =25</P> Fri, 01 Dec 2017 16:30:30 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317572#M94992 vrmandadi 2017-12-01T16:30:30Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317573#M94993 <P>So, considering your sample data of </P> <PRE><CODE>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 </CODE></PRE> <P>The output should be (single row, one column for each host)</P> <P>h1=(2334 +5580+9788 )/3 (which is number days h1 had data) , OR<BR /> h1=(2334 +5580+9788 )/7 (which is the total number of hosts) , OR something else?? </P> Fri, 01 Dec 2017 16:36:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317573#M94993 somesoni2 2017-12-01T16:36:29Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317574#M94994 <P>So you want a results table which would would look like this: ?</P> <PRE><CODE> 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 </CODE></PRE> Fri, 01 Dec 2017 16:38:05 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317574#M94994 nickhills 2017-12-01T16:38:05Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317575#M94995 <P>meh, formatting,, you get the point</P> Fri, 01 Dec 2017 16:38:30 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317575#M94995 nickhills 2017-12-01T16:38:30Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317576#M94996 <P>h1=(2334 +5580+9788 )/3 (which is number days h1 had data) ..This is what I am looking for</P> Fri, 01 Dec 2017 16:40:27 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317576#M94996 vrmandadi 2017-12-01T16:40:27Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317577#M94997 <P>or.. do you just want the last row? - ie just the averages?</P> Fri, 01 Dec 2017 16:40:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317577#M94997 nickhills 2017-12-01T16:40:29Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317578#M94998 <P>Ya correct I want the same thing like the table you showed with totals and the last row with avg</P> Fri, 01 Dec 2017 16:45:25 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317578#M94998 vrmandadi 2017-12-01T16:45:25Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317579#M94999 <P>Would this give not you what you need?<BR /> index=abc sourcetype=xy.. |stats avg(host) by host<BR /> run over 7 days</P> Fri, 01 Dec 2017 16:45:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317579#M94999 nickhills 2017-12-01T16:45:29Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317580#M95000 <P>nope,I tried that earlier and it does not give you the _time field also</P> Fri, 01 Dec 2017 16:48:30 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317580#M95000 vrmandadi 2017-12-01T16:48:30Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317581#M95001 <P>actually thats total rubbish - it wont even work. (commenting on my phone)</P> Fri, 01 Dec 2017 16:50:05 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317581#M95001 nickhills 2017-12-01T16:50:05Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317582#M95002 <P>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.</P> <P>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.</P> <PRE><CODE>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) </CODE></PRE> <P>Calculating this average every 7 days gets a bit more complicated - I believe eventstats would be a better solution in that case.</P> <P>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 <BR /> nickhillscpl mentioned.</P> <PRE><CODE>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) </CODE></PRE> <P>Hope this helps.</P> <P>EDIT: I see from the comments you are looking to retain the formatting you have in the table.. THis should do the trick:</P> <PRE><CODE> yoursearch | timechart span=1d count by host | addtotals | appendpipe [stats avg(*) as *] </CODE></PRE> Fri, 01 Dec 2017 16:59:55 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317582#M95002 c_boggs 2017-12-01T16:59:55Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317583#M95003 <P>Did you get a chance to test this query?</P> Fri, 01 Dec 2017 17:12:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317583#M95003 somesoni2 2017-12-01T17:12:29Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317584#M95004 <P>yup it gives you two columns one with host names and the other avg(host) with no values</P> Fri, 01 Dec 2017 17:14:09 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317584#M95004 vrmandadi 2017-12-01T17:14:09Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317585#M95005 <P>Give this a try</P> <PRE><CODE>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 </CODE></PRE> Fri, 01 Dec 2017 17:17:03 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-of-a-field-value-for-n-number-of/m-p/317585#M95005 somesoni2 2017-12-01T17:17:03Z