Splunk Enterprise Security

Compare today's values to the week's median without join

Hello there,

I'm have a search that get the events atributed to "N" number of users, and I would like to compare the total amount of today's events to whe weeks median (not average). My base search looks something like this:

    index=myindex earliest=-w@d  
| timechart span=1d count(events) by user limit=0

Which gives me this output:

_time user1 user2 userN
"day1" 1 1 4
"day2" 2 5 2
"day3" 6 7 7
.
.
"today" 3 8 6

I'll like to compare "today" total events with the median of the week (day 1 through today) for each user, returning the users that report 50% over or under the median. I managed to do this with join, since couldn't get it done with timechart/timewrap, but the search is really slow:

 index=myindex  earliest=@d 
| stats count(events) as today_totals by user
| join user [search index=myindex earliest=-w@d 
| bucket span=1d _time | stats count by _time user 
| stats median(count) as median_user 
| where today_totals>(median_user/0.5) OR today_totals<(median_user*0.5)

Any way to do this without join?

Thanks

0 Karma
1 Solution

Ultra Champion
...
| eventstats median(*) as *_med
| tail 1
| transpose 0 column_name=user
| eval name=mvindex(split(user,"_"),0)
| rename "row 1" as counts
| stats values(eval(if(match(user,"med"), counts, NULL))) as median values(eval(if(!match(user,"med"), counts, NULL))) as today by name
| where (today > median * 1.5) OR (today < median * 0.5)

I don't know your log and query. please fix it.

View solution in original post

Ultra Champion
...
| eventstats median(*) as *_med
| tail 1
| transpose 0 column_name=user
| eval name=mvindex(split(user,"_"),0)
| rename "row 1" as counts
| stats values(eval(if(match(user,"med"), counts, NULL))) as median values(eval(if(!match(user,"med"), counts, NULL))) as today by name
| where (today > median * 1.5) OR (today < median * 0.5)

I don't know your log and query. please fix it.

View solution in original post

Hey there. Thanks for your reply. Added my base search and the working search using join. I tried your solution but it gives me all zeros for "today's" column.

I'm sorry if my question wasn't clear.

0 Karma

Ultra Champion
_time user1 user2 userN
"day1" 1 1 4
"day2" 2 5 2
"day3" 6 7 7

well, your sample is wrong.

| stats count(events) as today_totals by user 's result:

user today_totals
userA 10
userB 20
....

I can't make query with wrong sample.

0 Karma

Managed to make it work with your solution combined with timechart! Thanks

0 Karma

Thanks again. My sample is correct, it gives me the daily events by "users". However, the line you just quoted is from my alternative solution using "join", which changes my base search, no longer using timechart.

I tried using timewrap, but couln't get there.

0 Karma