How to use values from inputlookup for date comparison?

[Jochen_Widmaier]

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?

[Jochen_Widmaier]

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. 

[ITWhisperer]

Try something like this

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

[Jochen_Widmaier]

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?!?

[ITWhisperer · Accepted solution]

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]

[Jochen_Widmaier]

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

[ITWhisperer]

What error message are you getting?