topic Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields? in Splunk Search

How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

danje57 — Mon, 12 Feb 2018 14:06:44 GMT

Hi,

I need your help as I think I didn't use Lookup correctly.

I've a field in my logs called source and which contain the complete path of a file.

/usr/home/logreader/20180212/2080212_CORP_all_AD_SUCESS_ACCESS.csv
/usr/home/logreader/20180212/2080212_CORP_all_AD_DENIED_ACCESS.csv
/usr/home/logreader/20180212/2080212_CORP_all_FW_SUCESS_ACCESS.csv
/usr/home/logreader/20180212/2080212_CORP_all_FW_DROP_ACCESS.csv

I created a Lookup named CORP_script_source.csv which contain:

source
CORP_all_AD_SUCESS_ACCESS.csv
CORP_all_AD_DENIED_ACCESS.csv
CORP_all_FW_SUCESS_ACCESS.csv
CORP_all_FW_DROP_ACCESS.csv

I would like to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields.

the query should be, but I don't how to do it

Indeed the query should make a match between the source field and the lookup. As I can't make an exact match

index=all_logs source="*CORP*" | dedup source | table source [|inputlookup CORP_script_source.csv source.... ] ...

Do you have any ideas??

Thanks in advance.

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

493669 — Mon, 12 Feb 2018 14:24:51 GMT

try this:

index=all_logs source="*CORP*" | dedup source|rex field=source ".*\/\d+_(?<source>.*)" |JOIN type=inner source [|inputlookup CORP_script_source.csv ]

it will join your index query with lookup by source field

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

danje57 — Tue, 29 Sep 2020 18:02:52 GMT

Thanks for your help,

I tried your suggest however the resut is Not results found.

Can I display a table to debug which give me:

CORP_all_AD_SUCESS_ACCESS.csv FOUND
CORP_all_AD_DENIED_ACCESS.csv NOT_FOUND
CORP_all_FW_SUCESS_ACCESS.csv NOT_FOUND
CORP_all_FW_DROP_ACCESS.csv FOUND

Your rex works fine as can display the table and it contains all sources which come from my logs.

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

493669 — Mon, 12 Feb 2018 14:48:33 GMT

try updated query...if the source field is extracted correctly then it should join with lookup using source field

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

danje57 — Tue, 29 Sep 2020 18:00:03 GMT

I done it but the result is the same...

When I make the first part of the query:

index=all_logs source="CORP" |rex field=source ".\/\d+_(?.)" | dedup source | table source

I obtain this:
CORP_all_AD_SUCESS_ACCESS.csv
CORP_all_AD_DENIED_ACCESS.csv
CORP_all_FW_SUCESS_ACCESS.csv
CORP_all_FW_DROP_ACCESS.csv

However, when I put the rest of the query:

I don't have anything:
Message: No results found.

Do you think it's possible to have a table something like that:

Filename Yes/No

Depending if the filename from the log is found in the lookup.

I'm a little bit lost with lookup 😕

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

danje57 — Tue, 29 Sep 2020 18:00:09 GMT

Just find it:

I switched my splunk from linux to windows in this example so the rex is not the same i've adapted for windows path

To find not present in the CSV

To find present in CSV

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

493669 — Mon, 12 Feb 2018 17:23:07 GMT

so is your issue resolved now?

Re: How to use the lookup table to find if I can retrieve the filename from my lookup in my log, using the source fields?

danje57 — Mon, 12 Feb 2018 17:50:20 GMT

Yes! Thanks!