How to edit my inputlookup search to filter based on time difference?

[goodsellt]

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?

[dnitschke_splun]

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 ]

[goodsellt · Accepted solution]

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

[pgreer_splunk]

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.

[goodsellt]

Yea, that's my current work around since it appears you only can do a very limited set of operations within the |inputlookup framework.

[goodsellt]

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.