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]

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.

[493669 · Accepted solution]

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

[danje57]

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.

[493669]

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

[danje57]

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:

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

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 😕

[danje57]

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

index="tescsv" source="CORP" |rex field=source ".\d+_(?.)" | table source _time | search NOT [| inputlookup Corp_Source_Scripting.csv | table source]

To find present in CSV

index="tescsv" source="CORP" |rex field=source ".\d+_(?.)" | table source _time | search [| inputlookup Corp_Source_Scripting.csv | table source]

[493669]

so is your issue resolved now?

[danje57]

Yes! Thanks!