Developing for Splunk Enterprise

Filter time-based values from inputlookup by time picker range

evelenke
Contributor

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

0 Karma

dnitschke_splun
Splunk Employee
Splunk Employee

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
Communicator

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. 

0 Karma

dpataferreira
New Member

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$

0 Karma

peter_krammer
Communicator
<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
Builder

Exactly! I confirm that works also for me!

0 Karma

peter_krammer
Communicator

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
Communicator

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

marcoscala
Builder

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

0 Karma

marcoscala
Builder

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

Thanks a lot!!!
Marco

0 Karma

james_n
Path Finder

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

0 Karma

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!