topic Filter time-based values from inputlookup by time picker range in Splunk Dev

Filter time-based values from inputlookup by time picker range

evelenke — Thu, 12 Oct 2017 17:56:52 GMT

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

Re: Filter time-based values from inputlookup by time picker range

niketn — Thu, 12 Oct 2017 18:42:43 GMT

@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?childToView=578991#answer-578991

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

Re: Filter time-based values from inputlookup by time picker range

peter_krammer — Tue, 29 Sep 2020 19:38:07 GMT

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

Re: Filter time-based values from inputlookup by time picker range

marcoscala — Wed, 18 Jul 2018 08:37:53 GMT

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

Re: Filter time-based values from inputlookup by time picker range

marcoscala — Tue, 29 Sep 2020 20:28:24 GMT

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

Thanks a lot!!!
Marco

Re: Filter time-based values from inputlookup by time picker range

dpataferreira — Tue, 29 Sep 2020 20:30:53 GMT

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$

Re: Filter time-based values from inputlookup by time picker range

peter_krammer — Wed, 18 Jul 2018 23:12:30 GMT

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

Re: Filter time-based values from inputlookup by time picker range

marcoscala — Thu, 19 Jul 2018 05:25:40 GMT

Exactly! I confirm that works also for me!

Re: Filter time-based values from inputlookup by time picker range

james_n — Wed, 08 Apr 2020 17:05:07 GMT

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

Re: Filter time-based values from inputlookup by time picker range

dnitschke_splun — Sat, 02 May 2020 15:03:04 GMT

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.

Re: Filter time-based values from inputlookup by time picker range

peter_krammer — Tue, 23 Mar 2021 09:41:33 GMT

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.

Re: Filter time-based values from inputlookup by time picker range

peter_krammer — Tue, 23 Mar 2021 09:40:28 GMT

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")