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!

Automatic Discovery Part 1: What is Automatic Discovery in Splunk Observability Cloud ...

If you’ve ever deployed a new database cluster, spun up a caching layer, or added a load balancer, you know it ...

Real-Time Fraud Detection: How Splunk Dashboards Protect Financial Institutions

Financial fraud isn't slowing down. If anything, it's getting more sophisticated. Account takeovers, credit ...

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...