I want to compare an indexed file with lookup table, where lookup table field is substring of indexed file field
Sample Indexed file:
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:
ABC.DFG
DEF.DF.
QWE.DFG.ABC.
DBC.D
QASDFG
ASDDFG.ABC.ZXC.MKL
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."*" ]
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."*" ]
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.
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
Thanks a ton, you saved my day 🙂
How do you want to compare? Do you want to filter only the events with file_name pattern not present in lookup?
Thanks for your reply, I want to filter events with file_name pattern present in lookup