topic Re: Trouble with MultiValue Fields in Splunk Search

Trouble with MultiValue Fields

dealmak3r — Mon, 28 Sep 2020 10:01:46 GMT

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!

Re: Trouble with MultiValue Fields

gkanapathy — Thu, 27 Oct 2011 06:30:58 GMT

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

Re: Trouble with MultiValue Fields

dealmak3r — Fri, 28 Oct 2011 04:54:16 GMT

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!