Splunk Search

compare data that not include in lookup file

rahmatn
Path Finder

Dear All, 
Need your help


I have case  to compare transaction data with lookup file, for example i have lookup file account.csv

it contain :

NameAccountNo
Jack1234
Bobby4321
Bobby3214
Donny7890

 

and then i have daily transaction like :

NameAccountNoAmount
Bobby43211000
Jack1234500
Donny7890500
Bobby88885000



i want to marking this daily transaction base on Name that has no AccountNo in lookup table account.csv
the marking can be a note or something else

thanks in advance for your help

Rahmat

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @rahmatn,

I suppose that you're using DB-Connect to query data.

usually DB-Connect is used to extract data (using an SQL Query) and put them in an index.

When you have data in an index, you can run a search like the one I hinted.

Anyway, if the problem is how to use the search, you could run something like this:

your_query
| search NOT [ | inputlookup account.csv | fields Name ]
| table Name AccopuntNo Amount

put attention that the field name "Name" is the same in query output and lookup, otherwise you have to modify your search:

your_query
| rename query_name AS Name 
| search NOT [ | inputlookup account.csv | fields Name ]
| table query_name AccopuntNo Amount

Ciao

Giuseppe

View solution in original post

rahmatn
Path Finder

i mean may be i was wrong on how to use the lookup command 

0 Karma

rahmatn
Path Finder

Hi Guiseppe, 

Thanks for your response, but it not solve my case

Actually the transaction data coming from SQL and the lookup is in the splunk
so i have to run the SQL query first and then using lookup command as subsearch

i cannot use "NOT" before the subsearch, or may be i 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @rahmatn,

I suppose that you're using DB-Connect to query data.

usually DB-Connect is used to extract data (using an SQL Query) and put them in an index.

When you have data in an index, you can run a search like the one I hinted.

Anyway, if the problem is how to use the search, you could run something like this:

your_query
| search NOT [ | inputlookup account.csv | fields Name ]
| table Name AccopuntNo Amount

put attention that the field name "Name" is the same in query output and lookup, otherwise you have to modify your search:

your_query
| rename query_name AS Name 
| search NOT [ | inputlookup account.csv | fields Name ]
| table query_name AccopuntNo Amount

Ciao

Giuseppe

gcusello
SplunkTrust
SplunkTrust

Hi @rahmatn,

you could run something like this:

index=your_index NOT [ | inputlookup account.csv | fields Name ]
| table Name AccopuntNo Amount

Ciao.

Giuseppe 

Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...