Splunk Search

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

braveterry
Engager

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!

Tags (1)
0 Karma
1 Solution

ssadanala1
Contributor

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 !!

View solution in original post

0 Karma

ssadanala1
Contributor

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 !!

0 Karma

braveterry
Engager

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.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...