topic Re: Use values from inputlookup for date comparison in Splunk Search

How to use values from inputlookup for date comparison?

Jochen_Widmaier — Mon, 27 Sep 2021 22:04:09 GMT

Hi,

I want to create a dashboard, where a user has a drop down input to select a named time frame ($value$). The start and end date of the time frame are defined in a lookup table.

Each of my events has a milestone date. I want to filter to those events where the milestone date is between the start and end date from the lookup table.

I tried something like this:

index=my_index | where milestone_date_epoch > [inputlookup mapping_lookup WHERE time_frame = $value$ | eval startdate = strptime(Start_date, "%Y-%m-%d") | return startdate] | where milestone_date_epoch < [inputlookup mapping_lookup WHERE time_frame = $value$ | eval enddate = strptime(End_date, "%Y-%m-%d") | return enddate]

But I get an error message 😞 Can you help me to get this fixed?

Re: Use values from inputlookup for date comparison

ITWhisperer — Fri, 24 Sep 2021 12:44:26 GMT

What error message are you getting?

Re: Use values from inputlookup for date comparison

Jochen_Widmaier — Fri, 24 Sep 2021 12:51:50 GMT

The error message says:

Error in 'where' command: The operator at '="1630879200.000000"' is invalid.

I assume that the number is the epoch I have evaluated from the inputlookup.

Re: Use values from inputlookup for date comparison

ITWhisperer — Fri, 24 Sep 2021 13:52:50 GMT

Try something like this

Re: Use values from inputlookup for date comparison

Jochen_Widmaier — Fri, 24 Sep 2021 15:27:23 GMT

I tried your proposal. From the Splunk documentation I would have guessed it needs to work now (thank you for pointing me there, I didn't now the format command earlier). But unfortunately I get a new error message:

Error in 'where' command: Type checking failed. 'AND' only takes boolean arguments.

But I don't have any AND in my query?!?

Re: Use values from inputlookup for date comparison

ITWhisperer — Fri, 24 Sep 2021 15:35:13 GMT

index=my_index
| where milestone_date_epoch > [inputlookup mapping_lookup WHERE time_frame = $value$
    | head 1
    | eval query = strptime(Start_date, "%Y-%m-%d")
    | table query
    | format]
| where milestone_date_epoch < [inputlookup mapping_lookup WHERE time_frame = $value$
    | head 1
    | eval query = strptime(End_date, "%Y-%m-%d")
    | table query
    | format]

Re: Use values from inputlookup for date comparison

Jochen_Widmaier — Mon, 27 Sep 2021 06:03:20 GMT

Thank you very much @ITWhisperer . Now it is working fine 👍. You are a true legend.