Splunk Search

How to compare search result with a lookup file

LeandroKopke
Explorer

I have a lookup file with the following fields

original_login_name, client_net_address and Datase_Name
I have these my fields in my search for splunk, using the query

index=sql sourcetype="mssql:execution:dm_exec_sessions" host="*" | rename client_net_address AS "IP_Origem" current_database_name AS Datase_Name original_login_name AS "Usuário" |stats count by login_time, IP_Origem, Datase_Name, Usuário |sort - count

How do I make a comparison between the search and the lookup, so that it returns me to only results that are not the same as the original_login_name, client_net, address and Datase_name fields?

Examples:

Lookup:
Fields: original_login_name, client_net_address, Datase_name
Value: aud_cdt, 10.1.1.5, Teste

Logs:
Fields: original_login_name, client_net_address, Datase_name
Value: aud_cdt, 10.1.1.5, Teste
Value: aud_cdt, 10.1.1.8, Teste
Value: edt_aud, 10.1.1.5, Teste

The search should return the following results:
Value: aud_cdt, 10.1.1.8, Teste
Value: edt_aud, 10.1.1.5, Teste

0 Karma

somesoni2
Revered Legend

Try like this

index=sql sourcetype="mssql:execution:dm_exec_sessions" host="*" | rename client_net_address AS "IP_Origem" current_database_name AS Datase_Name original_login_name AS "Usuário" |stats count by login_time, IP_Origem, Datase_Name , Usuário 
| where NOT [| inputlookup YourLookupTable.csv | table original_login_name, client_net_address ,Datase_Name | rename original_login_name AS "Usuário" , client_net_address  as AS "IP_Origem"  ]|sort - count
0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...