Columns split by Sum and Average over _time

[glancaster]

Hello all,

I am having a terrible time trying to get this logic correct and maybe someone can chime in to help a bit.

I would like the sum of searches per day of all users split by the avg number of searches by user over 90 days but I can't seem to work it out just right. I am able to post the average of the total search across the chart but not the daily average.

index=_audit host=search-* earliest=-90d latest=-1d  search_id!=scheduler_* 
| bucket _time span=1d 
|stats count by _time 
| eventstats avg(count) as SearchAVG

Any help is appreciated. Thank you!

https://drive.google.com/file/d/0B7c2Ap0cICFka243RFlqeTVRVlU/edit?usp=sharing

[somesoni2 · Accepted solution]

Try this

index=_audit host=search-* earliest=-90d latest=-1d  search_id!=scheduler_* 
| bucket _time span=1d 
    |stats count by _time,user 
    |stats sum(count) as TotalSearches, avg(count) by _time

This gives total search count (from all users) and daily average (for all user, total searches that day divided by total users that day).

[glancaster]

This worked exactly like I needed! Here is what I ended up with:

index=audit host=search-* earliest=-90d latest=-1d |search search_id=scheduler* | timechart dc(search_id) AS SchedSearches span=1d| appendcols [search index=audit host=search-* earliest=-90d latest=-1d search_id=scheduler* user=* | bucket _time span=1d |stats count by _time, user |timechart avg(count) as "AVG Search Head Usage"]

Do you know how I could ask to forget any users who did not have more than 5 searches that day? I tried adding in

|stats count by user
|search count > 5

but thats yielded no results.

Cheers!

[glancaster]

_time, TotalSearches and AvgSearches (of all users) - all per day.

a graph over time with columns split by Total Searches per day and the Avg Searches by all users per day.

The part I can't work out is the avg of the dc(search_id) by users per day.

[somesoni2]

What is the expected output?
_time...TotalSearches...AvgSearch

OR

_time...TotalSearches...AvgSearchUser1...AvgSearchUser2...