Splunk Search

SPL: How to perform a SQL Like Minus Operation?

ahendler1
Explorer

I am trying to remove certain logs from a base query of a certain type based on the results of another query of a different type of log. Both are connected by the user field.

Specifically, I have identified instances where a user has 4 or more failed login attempts, and am trying to remove instances where they successfully changed their password after. This leaves a list of users, and their associated logs, who have a large number of failed logins but did not update their password.

Here is the base query:

index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now 
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]

Here is the query I am essentially trying to include. However, SPL only handles left, right, and inner joins

| MINUS user [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now]

How might I accomplish this?

alt text

Thank you for your help.

Tags (1)
0 Karma
1 Solution

horsefez
Motivator

Hi @ahendler1,

it's actually not that difficult.

Go for something like this:

index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now 
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]
| search NOT [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now | fields user | format]

Tell me if it works.

View solution in original post

horsefez
Motivator

Hi @ahendler1,

it's actually not that difficult.

Go for something like this:

index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now 
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]
| search NOT [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now | fields user | format]

Tell me if it works.

ahendler1
Explorer

@pyro_wood

Works great, thank you!

0 Karma

horsefez
Motivator

@ahenler1,

great!
Please accept the answer then. :slightly_smiling_face:

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