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?
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.
ev1.UserID != ev2.UserID
Now that it's fixed, could you please re-check your answer?