I'm trying to set up a kvstore lookup where the results from inputlookup can be filtered using the regular time-pickers available on the web GUI or with the latest= and earliest= modifiers.
$ collections.conf
[testkv]
enforceTypes = true
field.action = string
field.ts = time
$ transforms.conf
[testkv]
external_type = kvstore
fields_list = action, ts
time_field = ts
;time_format = %s.%3N
;time_format = %s.%Q
The ts field contains a UNIX epoch with milliseconds so 10+3 digits.
Regardless what I select "Last 15 minutes", "Last 4 hours" I always get the whole kvstore content.
First of all, is that doable in general and, if yes, any ideas on what's wrong? 🙂
Sure, but not in a normal way. Do it like this:
| makeresults
| addinfo
| map
[| inputlookup testkv
| search ts>=$info_min_time$ AND ts<=$info_max_time$]
Sure, but not in a normal way. Do it like this:
| makeresults
| addinfo
| map
[| inputlookup testkv
| search ts>=$info_min_time$ AND ts<=$info_max_time$]
Why should you have to explicitly filter by time with a "search" or "where" command for a kvstore lookup when you don't have to with a regular search from an index?! This is a terrible approach. If it's the only approach to filtering a kvstore lookup by time then shame on Splunk.
You can also add the time filter into the WHERE
clause of inputlookup
, e.g.
| inputlookup testkv WHERE
[| makeresults count=1
| addinfo
| eval info_max_time=if(info_max_time=="+Infinity", 2147483647, info_max_time)
| eval search="( (ts>=" . info_min_time . ") AND (" . "ts<" . info_max_time . ") )"
| table search ]
Many thanks! That's a really interesting approach 🙂
I've just added a small workaround to handle the "All time" case and it seems to work as expected, I can simply create a dedicated macro now to make it more handy.
| makeresults
| addinfo
| eval info_max_time=if(info_max_time=="+Infinity", 9999999999999, info_max_time)
| map
[| inputlookup testkv
| search ts>=$info_min_time$ AND ts<=$info_max_time$]
Do you know any sort trick to cast that "+Infinity" so I can directly compare it with my ts field?
I should have caught that. I would do it exactly as you have done