Archive

Advanced filtering on |inputlookup command

ololdach
Builder

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)

Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Try turning the query around.

| inputlookup kvbig append=true where (time>[|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | return $testme]) |...
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Try turning the query around.

| inputlookup kvbig append=true where (time>[|makeresults | addinfo | eval testme=round(info_min_time-3600,0) | return $testme]) |...
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

ololdach
Builder

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!

0 Karma

ololdach
Builder

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

0 Karma