Getting Data In

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

goodsellt
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
1 Solution

goodsellt
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

dnitschke_splun
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

goodsellt
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
0 Karma

pgreer_splunk
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

goodsellt
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

goodsellt
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...