Dashboards & Visualizations

Columns split by Sum and Average over _time

glancaster
Path Finder

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

0 Karma
1 Solution

somesoni2
Revered Legend

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).

View solution in original post

somesoni2
Revered Legend

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
Path Finder

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!

0 Karma

glancaster
Path Finder

_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.

0 Karma

somesoni2
Revered Legend

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

OR

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...