Splunk Search

How to query a lookup table based on time

nirmalya2006
Path Finder

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

Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
| 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.

View solution in original post

woodcock
Esteemed Legend

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
Ultra Champion
| 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
Path Finder

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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 *
---
If this reply helps you, Karma would be appreciated.

nirmalya2006
Path Finder

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

nirmalya2006
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...