kvstore, inputlookup and time-bounds

[lfrit]

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? 🙂

[woodcock · Accepted solution]

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$]

[frechette]

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.

[dnitschke_splun]

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 ]

[lfrit]

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?

[woodcock]

I should have caught that. I would do it exactly as you have done