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.
| 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
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
hi @kmfpo,
You can also use accum command to calculate the running total of duration values.
| stats count, sum(duration) as duration, last(closed) as closed by closed_month
| eval closed=strptime(closed, "%b-%d")
| sort closed
| accum duration as total_duration
| accum count as total_count
| eval Avg=round(total_duration/total_count, 1), temp=1, closed_month="Avg ".closed_month
| xyseries temp, closed_month, Avg
| fields - temp
If this reply helps you, a like would be appreciated.
| 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
@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...)?
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
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