How do I write this SQL query as a join search in Splunk?

[pm771]

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?

[sundareshr]

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

[pm771]

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