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

[rajchi]

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

[somesoni2 · Accepted solution]

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."*" ] 

[rajchi]

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.

[somesoni2]

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

[rajchi]

Thanks a ton, you saved my day 🙂

[somesoni2]

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

[rajkumar10]

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