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

[omarguzmancamac]

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

[to4kawa · Accepted solution]

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

[omarguzmancamac]

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.

[to4kawa]

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

[omarguzmancamac]

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

[omarguzmancamac]

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.