How can I find users who had a particular event occur more than once within X days?

[braveterry]

I want to get a list of all users who had a particular event occur less than 30 days apart.

The events look roughly like this:

2018-05-03 09:14:41,173 INFO - [biscuitking@sesamestreet.com] - jsonResponse={  "responseHeader" : {    "success" : "true",    "serviceName" : "web-service",    "resourceName" : "resource-name",    "operationName" : "operation-name",    "version" : "1.2.3",    "transactionId" : "225b9c5b-384f-4e3a-bf3d-035ddb7cb45b",    "duration" : "1234",    "exitTimestamp" : "20180503 09:14:41.137 EDT",    "messages" : [ {      "code" : "UPDATE_SUCCESS",      "message" : "The operation was successful"    } ]  }}

In this example, USER_NAME=biscuitking@sesamestreet.com. I want to find all of the users over the last 6 months where events with these fields...

• resourceName=resource-name
• operationName=operation-name
• code=UPDATE_SUCCESS

... occur less than 30 days apart.

Thanks!

[ssadanala1 · Accepted solution]

You need to structure you query like the below

In your case modifications would be

base search resourceName=resource-name operationName=operation-name code=UPDATE_SUCCESS |table _time USER_NAME resourceName operationName code | eval epoch=strptime(_time, "%Y-%m-%d %H:%M:%S,%3N")
| sort _time USER_NAME
| streamstats current=f window=1 last(epoch) as lastts by USER_NAME
| eval timediff = epoch - lastts | fieldformat timediff = tostring(timediff, "duration")| where timediff < 60*60*24*30

Below is the query I used for generating data testing the query

| makeresults
| eval raw = "01/01/2018 17:00:00%2%hi%21%#02/03/2018 17:00:12%3%hi%39%#02/21/2018 17:12:00%1%man%3%#02/23/2018 17:12:00%2%hi%6%"
| makemv raw delim="#"
| mvexpand raw
| rex field=raw "(?P[^\%]+)%(?P[^\%]+)%(?P[^\%]+)%(?P[^\%]+)"
| table time customers name amount
| eval begin_epoch=strptime(time, "%m/%d/%Y %H:%M:%S")
| sort name _time
| streamstats current=f window=1 last(begin_epoch) as lastts by name
| eval timediff = begin_epoch - lastts | fieldformat timediff = tostring(timediff, "duration")| where timediff < 60*60*24*30

Happy Splunking !!

[braveterry]

Thanks! This worked for me with one modification. I had to change

eval epoch=strptime(_time, "%Y-%m-%d %H:%M:%S,%3N")

to:

eval epoch=_time

Maybe the order of operations matters in some way that I'm missing, but when I interrogate the _time value outside of the table command, it shows as a timestamp.