topic Re: compare data that not include in lookup file in Splunk Search

compare data that not include in lookup file

rahmatn — Thu, 10 Feb 2022 10:00:27 GMT

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

Re: compare data that not include in lookup file

gcusello — Thu, 10 Feb 2022 10:19:58 GMT

Hi @rahmatn,

you could run something like this:

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

Ciao.

Giuseppe

Re: compare data that not include in lookup file

rahmatn — Thu, 10 Feb 2022 10:55:31 GMT

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

Re: compare data that not include in lookup file

rahmatn — Thu, 10 Feb 2022 10:56:13 GMT

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

Re: compare data that not include in lookup file

gcusello — Thu, 10 Feb 2022 11:20:24 GMT

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:

Ciao

Giuseppe