I'm attempting to filter my inputlookup command based on the amount of time that has passed between "now" (when the job is run) and a field in the table which is a integer representation of the epoch time.
I'm attempting to do something like:
|inputlookup my_kvstore where 2700<=now()-Last_PA_Send
However, I'm getting the error: Error in 'SearchOperator:inputcsv': The ‘2700<=now()-Last_PA_Send’ filter could not be verified. It may contain invalid operators, or could not be optimized for search results.
Any ideas how I could get a filter like this to work correctly and still utilize the performance benefits of a inputlookup filter?
Either of these two are the workaround, unfortunately you cannot use anything other than a limited set of operations for the where clause at the inputlookup level:
|inputlookup my_kvstore | where 2700<now()-Last_PA_Send
|inputlookup my_kvstore | eval myTime=now()-Last_PA_Send | where myTime<=2700
What about creating a subsearch that generates the constraints for the WHERE
clause of the inputlookup
command. Your requirement 2700<=now()-Last_PA_Send
is equivalent to Last_PA_Send<=now()-2700
.
| inputlookup my_kvstore WHERE [| makeresults count=1| eval max_delta=now()-2700 | eval search="(Last_PA_send<=" . max_delta . ")" | table search ]
Either of these two are the workaround, unfortunately you cannot use anything other than a limited set of operations for the where clause at the inputlookup level:
|inputlookup my_kvstore | where 2700<now()-Last_PA_Send
|inputlookup my_kvstore | eval myTime=now()-Last_PA_Send | where myTime<=2700
Have you tried something like the following:
|inputlookup my_kvstore | eval myTime=now()-Last_PA_Send | where myTime<=2700
stuffing the difference result into a new field might get you around that filter parsing issue.
Yea, that's my current work around since it appears you only can do a very limited set of operations within the |inputlookup framework.
Note, I do know that this will work as intended by doing:
|inputlookup my_kvstore | where 2700<now()-Last_PA_Send
Though I'm lead to believe there will be a performance impact once the size of the KV store grows arbitrarily large.