topic How to count cumulative average by month? in Splunk Search

How to count cumulative average by month?

kmfpo — Wed, 31 Mar 2021 06:48:25 GMT

Hello all. I am trying to find the average by closed_month, but I want the average duration to include events from previous months in its average.

So, average for Feb should include Jan + Feb. Average for March should include Jan + Feb + Mar. I figured out how to get the average for each month, but I don't know how to get include the previous months average_duration along with the current month.

Sample Table of Data

Case opened closed closed_month duration aaa Jan-01 Jan-31 Jan 30 bbb Feb-10 Feb-26 Feb 16 ccc Feb-13 Feb-28 Feb 15 ddd Feb-14 Feb-28 Feb 14 eee Feb-17 Mar-01 Mar 11 fff Feb-24 Mar-13 Mar 17 ggg Mar-03 Mar-11 Mar 8 hhh Mar-11 Mar-16 Mar 5 iii Mar-22 Mar-24 Mar 2

Avg Jan = (30) = 30
Avg Feb = (30+16+15+14)/4 = 18.8
Avg Mar = (30+16+15+14+11+17+8+5+2)/9 = 13.1

The desired result is a column chart, with 3 columns, one for each resolved month. Then each would go have the value 30, 18.8, and 13.1 respectively.

Re: How to count cumulative average by month?

gcusello — Wed, 31 Mar 2021 06:55:34 GMT

Hi @kmfpo,

in other words, you want data from the 1st of january of the current year to the last day of the previous month, is this correct?

If this is your need, you habe to add to your main search these time qualifiers:

earliest=@y latest=@mon

Ciao.

Giuseppe

Re: How to count cumulative average by month?

ITWhisperer — Wed, 31 Mar 2021 06:58:55 GMT

Re: How to count cumulative average by month?

manjunathmeti — Wed, 31 Mar 2021 07:59:12 GMT

hi @kmfpo,

You can also use accum command to calculate the running total of duration values.

If this reply helps you, a like would be appreciated.

Re: How to count cumulative average by month?

kmfpo — Wed, 31 Mar 2021 13:45:03 GMT

@ITWhisperer The only thing wrong that needs adjustment is the month sorting. It is listed as Feb, Jan, Mar (alphabetical I am guessing). How can I force the order to be calendar order (Jan, Feb, Mar...)?

Re: How to count cumulative average by month?

scelikok — Wed, 31 Mar 2021 14:18:44 GMT

Hi @kmfpo,

Since the closed_month is not time, time conversion functions are not working. You can use below;

| makeresults | eval _raw="Case opened closed closed_month duration aaa Jan-01 Jan-31 Jan 30 bbb Feb-10 Feb-26 Feb 16 ccc Feb-13 Feb-28 Feb 15 ddd Feb-14 Feb-28 Feb 14 eee Feb-17 Mar-01 Mar 11 fff Feb-24 Mar-13 Mar 17 ggg Mar-03 Mar-11 Mar 8 hhh Mar-11 Mar-16 Mar 5 iii Mar-22 Mar-24 Mar 2" | multikv forceheader=1 | fields - _raw _time | streamstats sum(duration) as total count as cases | stats max(cases) as cases max(total) as total by closed_month | eval average=total/cases | eval month=case( closed_month="Jan","01", closed_month="Feb","02", closed_month="Mar","03", closed_month="Apr","04", closed_month="May","05", closed_month="Jun","06", closed_month="Jul","07", closed_month="Aug","08", closed_month="Sep","09", closed_month="Oct","10", closed_month="Nov","11", closed_month="Dec","12") | sort month | fields - month

Re: How to count cumulative average by month?

ITWhisperer — Wed, 31 Mar 2021 14:19:01 GMT

You should parse the date strings to epoch time values

| eval time=strptime(time, "%Y-%m-%d %H:%M:%S")

or what time format you are using in your real events