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

0 Karma
Get Updates on the Splunk Community!

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

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...