How to query a lookup table based on time

[nirmalya2006]

HI All

I have a lookup table which is populated by a scheduled search once everyday.
The lookup table looks like below

 Tickets, Cases, Events, _time 
 10,      11,    45,     2019-11-01 
 14,      15,    79,     2019-11-02
 11,      22,    84,     2019-11-03

The query used to populate the lookup table is as below

<index> <base search>
| timechart span=1d count by actionItem

Here the actionItems are Tickets, Cases, Events
All this is fine, lookup is created, lookup is populating and fetching etc.

But when I want to query the lookup table based on time, I am unable to do so.
I tried using the below queries but none of them worked.

| inputlookup lookup.csv 
| where strptime(_time, "%Y-%m-%d") >= "2019-11-01"
| table *

| inputlookup lookup.csv 
| search _time >= "2019-11-01"
| table *

| inputlookup cases_and_events.csv 
| search earliest="11/01/2019:00:00:00" latest="11/04/2019:00:00:00"
| table *

Can someone please point me to the right keywords to fetch the details based on _time.
I understand that it seems the confusion is created because I have the column name as _time in thelookup csv
This is because the initial look up load query was created using timechart.

Thanks

[woodcock]

This will use the Time picker to control your time filter:

| inputlookup cases_and_events.csv 
| eval _time = strptime(_time, "%Y-%m-%d")
| addinfo | rename info_* AS *
| where _time >= _info_min_time AND _time <= _info_max_time

[to4kawa · Accepted solution]

| makeresults
| eval _raw="Tickets, Cases, Events, _time 
  10,      11,    45,     2019-11-01 
  14,      15,    79,     2019-11-02
  11,      22,    84,     2019-11-03"
| multikv forceheader=1
| rename time_ as _time
| foreach * 
    [ eval <<FIELD>> = ltrim(<<FIELD>>," ") ]
| eval _time=strptime(_time,"%Y-%m-%d")
| table Tickets, Cases, Events, _time
`comment("this is sample data, please use inputlookup")`
| where _time >= strptime("2019-11-01","%Y-%m-%d")
| table _time, Tickets, Cases, Events

Hi, @nirmalya2006
Since your CSV's _time is a string, you cannot compare large and small at first.
Therefore, you need to change the strings to hours ( strptime )and compare them as hours.
If the table order is changed in this way, a line chart can be displayed.

[nirmalya2006]

Bang on .. Thank you so much.
I was thinking that _time in the csv was causing the problem.

[richgalloway]

One cannot compare time strings, except [in]equality. To find out if one time field is greater than another you must first convert them to integers. Try this:

| inputlookup lookup.csv 
| eval time=strptime(_time, "%Y-%m-%d")
| search time >= relative_time(now(), "@m")
| table *

[nirmalya2006]

Thanks @richgalloway, but this doesn't return any records.
Basically I want to fetch the records between 11-01-2019 and 11-04-2019 irrespective of the current date.

[gcusello]

Hi @nirmalya2006,
did you explored the choice to use Summary index instead a time based lookup?
In your scheduled searches you have to add the command | collect index=my_summary_index instead outputlookup.
Then you can use the same approach of normal searches.

Ciao.
Giuseppe

[nirmalya2006]

Hi @gcusello, I did look at summary index, but my administrator has disabled it and the current need has too little data to be of good enough for summary index.