Hello community!
We're really stuck on a question ... Basically we are trying to do ask the following question
For users who installed on day X, what % came back on day X+1?
1) We have installs logged sourcetype="smartypants_app" action="remote_auth" install=true | timechart span=1day values(user) as user dc(user) as installs
2) We have logins sourcetype="smartypants_app" action="remote_auth" | timechart span=1day values(user) as user dc(user) as logins
If this was SQL we would do something like
SELECT * FROM remote_auth installs, remote_auth logins WHERE installs._time BETWEEN x AND x+1 AND logins._time BETWEEN x+1 AND x+2 AND installs.user = logins.user GROUP BY installs.user
Problem: how to do this in Splunk?
Many thanks for anyone willing to help!
This is a bit of a trick, but you could do:
sourcetype=smartypants_app action=remote_auth earliest=-2d@d latest=-1d@d
| append [ search sourcetype=smartypants_app action=remote_auth install=true earliest=-3d@d latest=-2d@d ]
| bucket _time span=1d
| stats min(_time) as firstseen,max(_time) as lastseen by user
| stats count(eval(firstseen<=relative_time(now(),"-3d@d")) as installedonday1
count(eval(firstseen!=lastseen)) as installed_and_returned
by user
| eval installed_and_returned/installedonday1
This particular example isn't too generalizable to (for example) getting day x+1 and day x+2 users in the same query and differentiating, but it is possible to slightly rewrite it to be more tweakable (though longer).
Hi gkanapathy,
This answer is great! Except.... we actually want to repeat for N days.
So using the sample group of users from 7 days ago, we want to know the % who came back on day 2-5....
so i can't hard code -3d@d/-2d@d as in your proposal... can splunk do a range like this?
Thanks!