Filter time-based values from inputlookup by time picker range

[evelenke]

Hi Splunkers,

I have csv tables (inputlookup) with latest time of particular event for users, sources..., reflected in field _time . These tables are utilized as filters for my dashboard with statistics (| inputlookup mylookup | fields user). This helps to decrease time of filtering for a long-time ranges for events in dashboard.

Is it possible to filter out values from inputlookup table output with time range chosen in a Time picker?
Something like | inputlookup mylookup | where _time>$timepicker.earliest$ AND _time<$timepicker.latest$| fields user

[dnitschke_splun]

In case your lookup file contains time in seconds since the epoch, you can also add the time filter into the WHERE clause of inputlookup, e.g.

| inputlookup Product_Status.csv WHERE
[| makeresults count=1
| addinfo
| eval info_max_time=if(info_max_time=="+Infinity", 2147483647, info_max_time)
| eval search="( (_time>=" . info_min_time . ") AND (" . "_time<" . info_max_time . ") )"
| table search ]

[peter_krammer]

Does doing it this way make it more performant over the alternative to filter afterwards?

If yes, could you explain where the performance improvement comes from? 

I am always interested to learn performance tricks for Splunk. 

[dpataferreira]

Hi,

how did you use your code in order to work?

I´ve the following:

• Time Range
• 
  
• 0
• 
  
• 
  

And then the query:

      <query>| inputlookup append=t Product_Status.csv  where "Product Origin" = "*" | eval_time = strptime(OpenDate,"%d/%m/%Y")  | timechart span=1month count("Product Origin") as ProductOrigin</query>
      <earliest>$TimeRangePkr.earliest$</earliest>
      <latest>$TimeRangePkr.latest$</latest>

How you used the " info_min_time and info_max_time instead of $timeToken.earliest$ or >$timeToken.latest$" ?

Tks
,,Hi Marco. How you use the code in order to work?

I´ve a picker:

<input type="time" token="time" searchWhenChanged="true">
  <label>Time range</label>
  <default>
    <earliest>-30d@d</earliest>
    <latest>now</latest>
  </default>
</input>

and a query that don´t work:

| inputlookup append=t TestStatys.csv where "Produt Origin" = "*" | eval_time = strptime(OpenDate,"%d/%m/%Y") | timechart span=1month count("Product Origin") as Product
$time.earliest$
$time.latest$

[peter_krammer]

<query>
| inputlookup append=t Product_Status.csv  where "Product Origin" = "*" 
| eval _time = strptime(OpenDate,"%d/%m/%Y") 
| addinfo
| where _time>=info_min_time AND _time<=info_max_time
| timechart span=1month count("Product Origin") as ProductOrigin
</query>
<earliest>$time.earliest$</earliest>
<latest>$time.latest$</latest>

[marcoscala]

Exactly! I confirm that works also for me!

[peter_krammer]

I use the following, that works in any search and/or dashboard, report, alert, ...
| inputlookup mylookup.csv
| addinfo
| where _time>=info_min_time AND _time<=info_max_time

[peter_krammer]

I now use an updated version, that also compensates for choosing "All Time" in the time picker, which makes info_max_time set to "+Infinity" which unfortunately is not a number.

| inputlookup mylookup.csv
| addinfo 
| where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")

[marcoscala]

It works but not from a dashboard, using a shared timerange picker. Thanks for the idea anyway!

[marcoscala]

I correct! It works actually using info_min_time and info_max_time instead of $timeToken.earliest$ or >$timeToken.latest$

Thanks a lot!!!
Marco

[james_n]

@peter_krammer Thanks bro, for me also it's works fine in a dashboard.

[niketn]

@evelenke can you add the _time field values from your lookup file mylookup.csv? Your life would be easy if you store time in YYYY/MM/DD HH:MM:SS format.

In order to pass time from Time Picker over to your inputlookup, you will require two things:

1) Convert epoch time to string time in YYYY/MM/DD HH:MM:SS. Since csv file will have string time, ensure that this specific format is used to allow string time comparison (otherwise comparison will fail and you would need different approach to use epoch time instead).

2) Since time picker may not always have epoch time, it rather has the relative time with snap to notation, hence you would need to deduce the string time for selected earliest and latest time through time input change event handler. Refer to one of my answers for how to do this: https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html?ch...

3) You should be able to perform where clause in the base inputlookup command itself, rather than a separate pipe (which may lead to poor query performance based on the size of lookup file).