Advanced filtering on |inputlookup command

[ololdach]

A large kv lookup table (>2M entries and growing) holds metadata and is processed on a regular schedule to solve some complex correlations. The task at hand is to make accessing the last 5k entries more efficient.

The current search looks like this: |inputlookup kvbig | addinfo | where time>info_min_time | ... Runtime about 80s

To speed things up, I'd like to include the where in the lookup and tried:
|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | inputlookup kvbig append=true where (time>testme) |...

The above delivered all 2M results and did not work whereas the second attempt, hardcoding the start time:
|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | inputlookup kvbig append=true where (time>1570172400) |...

has worked like a charm returning the wanted 5k results in a splitsecond.

Question: How can I inject a calculated field/result/parameter into the inputlookup where clause that does NOT come from a UI token? (since it's a scheduled search, no such luck as to have tokens around)

[richgalloway · Accepted solution]

Try turning the query around.

| inputlookup kvbig append=true where (time>[|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | return $testme]) |...

[ololdach]

This is extremely cool. I never thought about inserting a subsearch to substitute a literal value! If I had more than 20 points, I'd reward you plenty. Thanks!

[ololdach]

Unfortunately the where clause in inputlookup doesn't support the full eval syntax. Otherwise we could have used something like "...where (time>now()-3600)"