We faced an issue with this solution : We need to filter a big time based KVSTore (several millions of lines)
where routes was not a success as it loads everything via the
inputlookup then filtering.
The solution we've found is has below :
| inputlookup lookup where [| makeresults | addinfo | table info_min_time | format "" "" "" "" "" "" | rex field=search "\"(?<search>[^\"]+)\"" ] AND _time<= [| makeresults | addinfo | table info_max_time | eval info_max_time=if(info_max_time="+Infinity",2999999999,info_max_time) | format "" "" "" "" "" "" | rex field=search "\"(?<search>[^\"]+)\""]
There may be an easier way to use the format but i'm not every good with this command 🙂
Hi @sbimizry ,
If you have not included a time value anywhere in your lookup, then you cannot do this. Lookup files are basically state tables that the owner defines and updates. This means that the owner also defines which fields to include in the lookup, which may or may not (most do not) have a field that references a time value. Even if it DOES reference a time value, it may not be the time value you are thinking of. You would need some logic that executes when you update / create your lookup to add a time value that equates to the execution time of the creation / update of the lookup. Once you have a time field, you can re-map it to the
_time field, which should allow you to use
search earliest=-24h@h (you don't need latest=now(), Splunk assumes that if you don't provide a
If you HAVE included a time field in your lookup then you can also use @woodcock 's solution above:
| inputlookup lookup
| where ( >= infomintime AND <= infomaxtime)
I hope this information provides you with your answer.
If you want to use earliest and latest mandatorily in your search, push your data to index. In setting -> Add Data -> Upload, select your CSV file. Now _time field value will be the same as timestamp value in your CSV file. After this, select an index or create a new index and add data and start searching.
OR if you want to use inputlookup, use this code at the start of query:
| inputlookup <lookup name>.csv | eval summarydateformat=round(strptime(<time-field>,"%m/%d/%y"),0) | where (summarydateformat >="$time_tok1.earliest$" and summarydateformat <="$time_tok1.latest$")
Replace time-field with the timestamp of your CSV file and time format accordingly. Add a date picker with token name as timetok1. Add "$timetok1.earliest$" and "$time_tok1.latest$" in your searches. But in this approach u will not be able to select "last 24 hr", "last 30 days" etc. U can only select To and From date from the date picker.