Splunk Search

How to use values from inputlookup for date comparison?

Jochen_Widmaier
Engager

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?

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
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]

View solution in original post

0 Karma

Jochen_Widmaier
Engager

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Jochen_Widmaier
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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]
0 Karma

Jochen_Widmaier
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What error message are you getting?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...