Getting Data In
Highlighted

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

Contributor

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?

0 Karma
Highlighted

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

Contributor

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.

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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.

0 Karma
Highlighted

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

Contributor

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

0 Karma
Highlighted

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

Contributor

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

View solution in original post

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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 ]

0 Karma