compare data that not include in lookup file

[rahmatn]

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 :

Name	AccountNo
Jack	1234
Bobby	4321
Bobby	3214
Donny	7890

and then i have daily transaction like :

Name	AccountNo	Amount
Bobby	4321	1000
Jack	1234	500
Donny	7890	500
Bobby	8888	5000

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

[rahmatn]

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

[rahmatn]

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 

[gcusello · Accepted solution]

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]

Hi @rahmatn,

you could run something like this:

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

Ciao.

Giuseppe