Splunk Search

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

pm771
Communicator

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?

0 Karma

sundareshr
Legend

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
0 Karma

pm771
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...