Splitting eval results by week - can't get timechart or bucket to work for me

[fuzzy_rocks]

Hi - I am measuring how many days in a week users are returning to our site, and calculating the ratio of users who return more than 4 days over the total. The following query works:

sourcetype="storage1" consumer_src="my_service" http_user_agent="*" 
| eval my_date_mday = strftime(_time, "%d")| stats dc(my_date_mday) AS DaysConnected by UserID 
| stats count(eval(DaysConnected<=4)) AS Occasionalusers  count(eval(DaysConnected>4)) AS FrequentUsers 
| eval UserLoyalty=FrequentUsers*100/(FrequentUsers+Occasionalusers)

I would like to be able to chart the results by week over the last 4 weeks, but when I try to replace stats with timechart like this:

sourcetype="storage1" consumer_src="my_service" http_user_agent="*" | eval my_date_mday = strftime(_time, "%d")| stats dc(my_date_mday) AS DaysConnected by UserID | timechart span=1w count(eval(DaysConnected<=4)) AS Occasionalusers  count(eval(DaysConnected>4)) AS FrequentUsers | eval UserLoyalty=FrequentUsers*100/(FrequentUsers+Occasionalusers)

... I get no results. I've tried bucket also, but seems to have no effect. Any help really appreciated!

[jhupka · Accepted solution]

It is most likely because you have blown away your _time value during the following portion of your search:

stats dc(my_date_mday) AS DaysConnected by UserID

When you use stats, pretty much the only fields you have left after the stats runs are the ones you specify in the command (DaysConnected and UserID in your case). Since commands like timechart and bucket usually rely on _time when doing time-based calculations, you need time back.

Depending on how you want to "mark" each UserID's time, you could potentially do something like this to keep an _time value around (and _time would essentially be the last time they showed up on your site:

stats dc(my_date_mday) AS DaysConnected, latest(_time) as _time by UserID 

So you'll now have a table of UserIds, the # days connected, and the last time we saw them. Since we now have a _time field you could use this for timechart stuff. So now that you got your _time back continue on with the calcs you wanted to do initially.

Another side note - are you inherently missing your built-in date_* fields that Splunk gives you for free from _time? For example, instead of doing your eval/strftime portion of your search you could just use the "free" date_mday field since Splunk already should have done this for you.

[fuzzy_rocks]

Correct! I was obliterating _time. Built-in time functions, report back 8 days in a week, probably b/c of timezones. latest(_time) didn't work over multiple weeks, the below query while not pretty does the job.

... | eval WorkWeek=strftime(_time, "%U")| eval my_day=strftime(_time, "%d") | eval Day_ID = my_day+";"+WorkWeek+";"+UserID | stats dc(Day_ID) AS DaysConnected by WorkWeek, UserID | stats count(eval(DaysConnected<=3)) AS OccasionalUsers count(eval(DaysConnected>3)) AS FrequentUsers by WorkWeek | eval UserLoyalty = FrequentUsers*100/(FrequentUsers+OccasionalUsers)