topic Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field in Splunk Search

Compare indexed file with lookup table where lookup table field is substring of indexed file field

rajchi — Tue, 29 Sep 2020 08:59:44 GMT

I want to compare an indexed file with lookup table, where lookup table field is substring of indexed file field

Sample Indexed file:

_time file_name

03/04/16 7:00 ABC.DFG.123
03/04/16 7:00 DEF.DF.A123
03/04/16 7:00 QWE.DFG.ABC.peter
03/04/16 7:00 DBC.D.030416
03/04/16 7:00 QASDFG.123
03/04/16 7:00 ASDDFG.ABC.ZXC.MKL123
03/04/16 7:00 DEF.DF.A123
03/04/16 7:00 QWE.DFG.ABC.peter
03/04/16 7:00 DBC.D.030416
03/04/16 7:00 QASDFG.123
03/04/16 7:00 QASDFG.123
03/04/16 7:00 ASDDFG.ABC.ZXC.MKL123
03/04/16 7:00 DEF.DF.A123
03/04/16 7:00 QWE.DFG.ABC.peter
03/04/16 7:00 DBC.D.030416

Sample Lookup table:

file_name

ABC.DFG
DEF.DF.
QWE.DFG.ABC.
DBC.D
QASDFG
ASDDFG.ABC.ZXC.MKL

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

somesoni2 — Mon, 07 Mar 2016 18:08:38 GMT

How do you want to compare? Do you want to filter only the events with file_name pattern not present in lookup?

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

rajkumar10 — Mon, 07 Mar 2016 18:13:47 GMT

Thanks for your reply, I want to filter events with file_name pattern present in lookup

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

somesoni2 — Mon, 07 Mar 2016 20:18:14 GMT

That would be easy.

Option 1: If you've access to update the lookup table file, update the lookup table to have an asterisk at the end of each value of file_name.
Sample Lookup table:

file_name
ABC.DFG*
DEF.DF.*
QWE.DFG.ABC.*
DBC.D*
QASDFG*
ASDDFG.ABC.ZXC.MKL*

Then run the search like this

index=foo sourcetype=bar [| inputlookup yourlookupname | table file_name]

This will filter to show only the events which file_name matching from lookup.

Option 2: If you can't update the lookup table content, then use this search

  index=foo sourcetype=bar [| inputlookup yourlookupname | table file_name | eval file_name=file_name."*" ]

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

rajchi — Tue, 29 Sep 2020 08:59:52 GMT

Thanks for your reply @somesoni2, really appreciate it. Somehow option 2 is not working, I can try option 1 as well but my lookup table have other columns too like "start_time" and "end_time". If indexed field(i.e. file_name) is matching to lookup table field(i.e. file_name) then I have to check whether _time(indexed file field) falls between "start_time" and "end_time"(lookup table field) or not.

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

somesoni2 — Tue, 29 Sep 2020 09:00:46 GMT

For 2nd option, when you say it's not working, do you get an error OR the filtering is not happening as expected?

You didn't specify your start_time/end_time condition earlier and that makes it complex. For that you would need to create a lookup definition using which you'd be able to do a lookup based on wildcard. (See this https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html). After that you can do like this to apply filters

your base search | lookup yourWildcardLookupDefinition OUTPUT start_time end_time | where isnotnull(start_time) AND _time>start_time AND _time<end_time

Re: Compare indexed file with lookup table where lookup table field is substring of indexed file field

rajchi — Mon, 07 Mar 2016 22:51:05 GMT

Thanks a ton, you saved my day 🙂