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
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 Product_Status.csv WHERE
[| makeresults count=1
| 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 ]
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.
how did you use your code in order to work?
I´ve the following:
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$" ?
,,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
<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>
I use the following, that works in any search and/or dashboard, report, alert, ...
| inputlookup mylookup.csv
| where _time>=info_min_time AND _time<=info_max_time
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")
@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).