topic Re: How to query a lookup table based on time in Splunk Search

How to query a lookup table based on time

nirmalya2006 — Fri, 22 Nov 2019 11:35:58 GMT

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

Re: How to query a lookup table based on time

gcusello — Fri, 22 Nov 2019 13:34:00 GMT

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

Re: How to query a lookup table based on time

richgalloway — Fri, 22 Nov 2019 14:07:22 GMT

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 *

Re: How to query a lookup table based on time

nirmalya2006 — Fri, 22 Nov 2019 23:08:54 GMT

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.

Re: How to query a lookup table based on time

nirmalya2006 — Fri, 22 Nov 2019 23:10:57 GMT

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.

Re: How to query a lookup table based on time

to4kawa — Fri, 22 Nov 2019 23:49:26 GMT

| 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.

Re: How to query a lookup table based on time

woodcock — Sat, 23 Nov 2019 06:34:44 GMT

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

Re: How to query a lookup table based on time

nirmalya2006 — Sat, 23 Nov 2019 11:41:00 GMT

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