topic cohorting with fixed first event dates in Splunk Search

cohorting with fixed first event dates

stephenreece — Fri, 17 Jan 2020 08:29:54 GMT

morning all,

I am struggling with the logic around doing this.

I am trying to run a report from 01/01/2018 to today looking at how many times (ideally in a range), that users are using the service. (what they do is almost irrelevant, just that they are on it is enough.

The challenge is 1 want 2 years of behaviour and not anyone using it within 2 years...

so ideally i need to isolate all users who entered in say 01/01/2018 to 01/02/2018 and then what their behaviour has been over the 2 years since.

What i have so far is:

sourcetype=mymainsourcetype
| stats values(_time) as source by clientIP

(this is ideal as i get to see ALL the users and their entries (however i now need to isolate just where the first entry is between 1st jan 18 and 1st feb 18.

any ideas would be great

Re: cohorting with fixed first event dates

stephenreece — Fri, 17 Jan 2020 08:40:42 GMT

i've found starttime=01/01/2018:00:00:00 endtime=02/01/2018:00:00:00 which is one step close

Re: cohorting with fixed first event dates

to4kawa — Fri, 17 Jan 2020 14:12:12 GMT

index=mymainindex sourcetype=mymainsourcetype starttime=01/01/2018:00:00:00 endtime=02/01/2018:00:00:00 clientIP=*
| stats count(_time) as appear_count min(_time) as first_appear max(_time) as last_appear range(_time) as duration list(_time) as time_list by clientIP
| foreach *_appear  [ fieldformat <<FIELD>>=strftime(<<FIELD>>,"%c")]

You should check the status of clientIP with this search and then consider the next search method.