table of multiple averages, each for a different time period

[ltruesda]

I have an index of events where each event is associated with a user. I want to produce a table where each row represents a user. Of course the first column will be the key column of userids.

The other three columns need to be event-per-day averages for three consecutive time periods. For example, the first time period could be "before July 1st, 2013", the second could be "from July 1st, 2013 through August 31st, 2013", and the third could be "after August 31st, 2013".

The intention is to split time into three consecutive time periods and calculate event-per-day averages for each time period.

Thanks for all assistance.

[lguinn2 · Accepted solution]

Try this

yoursearchhere
| bucket _time span=1d
| stats count by _time userId
| eval cutTime1 = strptime("7/1/2013","%m/%d/%Y")
| eval cutTime2 = strptime("8/31/2013","%m/%d/%Y")
| eval timePeriod = case(_time < cutTime1,"before July 1st, 2013",
                         _time >= cutTime1 AND _time <= cutTime2,"from July 1st, 2013 through August 31st, 2013",
                         _time > cutTime2,"after August 31st, 2013")
| chart average(count) by userId timePeriod

Look at the table output from the chart command (instead of the graphic) and I think it will be just what you asked for -- and it doesn't require subsearches, so it may be much faster...

[HiroshiSatoh]

Are you sure you want a like this?

(your search) earliest=-1d@d latest=@d |stats avg(Recordvalue) as Day1avg by UserID|join UserID [search (your search) earliest=-2d@d latest=-1@d |stats avg(Recordvalue) as Day2avg by UserID]|join UserID [search (your search) earliest=-3d@d latest=-2@d |stats avg(Recordvalue) as Day3avg by UserID]

result:
UserID Day1avg Day2avg Day3avg
A001 1 2 3
A002 4 5 6
A003 7 8 9

[HiroshiSatoh]

I used a sub-search because it did not know what average is, but I was glad to the average of the number of events. and advances the more you use the bucket.