I will ask my question using online forum as an example.
It has Event Log that tracks all user actions from login to logout. Contains the following significant fields: EventTime
, UserID
, IP address
, Action
.
We want to write a search that identifies users that were banned ( Action='Ban'
) and then quickly came back under another UserID
, but still with the same IP
.
In SQL I would use the following SELF JOIN
(assuming TimeDiff
is a valid function returning seconds):
SELECT ev1.IP, ev1.UserID, ev1.EventTime, ev2.UserID, ev2.EventTime, ev2.Action
FROM EventLog ev1
JOIN Eventlog ev2 ON ev1.IP = ev2.IP AND
ev1.UserID != ev2.UserID AND
ev1.EventTime < ev2.EventTime AND
TimeDiff(ev2.EventTime, ev1.EventTime) <= 60
WHERE ev1.Action = 'Ban'
How to write such a search in Splunk?
Try this
index=* [search index=* action=* | dedup IP | table IP] | eventstats count by IP | where count>1 | streamstats window=1 current=f latest(_time) as nextattempt by IP | eval time_diff=abs(nextattempt-_time) | where time_diff<=60
I had an unfortunate typo in my SQL query. Second line of ON
condition was incorrect, should've been ev1.UserID != ev2.UserID
.
Now that it's fixed, could you please re-check your answer?
TIA