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!

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