topic Re: How to edit my inputlookup search to filter based on time difference? in Getting Data In

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

goodsellt — Wed, 09 Nov 2016 16:21:54 GMT

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?

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

goodsellt — Wed, 09 Nov 2016 20:17:05 GMT

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.

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

pgreer_splunk — Mon, 14 Nov 2016 20:53:16 GMT

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.

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

goodsellt — Mon, 14 Nov 2016 21:53:04 GMT

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

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

goodsellt — Mon, 14 Nov 2016 21:55:15 GMT

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

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

dnitschke_splun — Sat, 02 May 2020 15:20:15 GMT

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.