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)
Try turning the query around.
| inputlookup kvbig append=true where (time>[|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | return $testme]) |...
Try turning the query around.
| inputlookup kvbig append=true where (time>[|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | return $testme]) |...
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!
Unfortunately the where clause in inputlookup doesn't support the full eval syntax. Otherwise we could have used something like "...where (time>now()-3600)"