Our top user ended up with the following query -
| inputlookup WHERE
[ | makeresults count=8 | streamstats count | eval WEEKSTART=relative_time($MYWEEKPICKER$,"-" + tostring(count - 1) + "w@w0")
| table WEEKSTART ]| rename WEEKSTART AS _time
The intention of makeresults count=8
is to create a dummy eight rows and by doing it, limit the results to only 8 rows starting with $MYWEEKPICKER$
. Can we do it in any other way?
You are looking to retrieve only 8 rows from a lookup table, and those 8 rows each contain a timestamp? So something else is setting a value to the token $MYWEEKPICKER$, and you want to find 8 rows whose timestamp is the same as that value? Or more recent than that value?
-- and you want to find 8 rows whose timestamp is the same as that value
Of the data in the lookup file, we are trying to find out the eight previous dates available based the value from the drop-down.
Assuming your timestamps are in a field called timestamp_field, try this:
|inputlookup mylookup | sort - timestamp_field | where timestamp_field<=$MYWEEKPICKER$ | head 8 | fields timestamp_field
You could always put a | head 8
command in the mix to restrict it to the first 8 results of the lookup file.
That's exactly the problem as we need to find the starting point and go back 8 rows.
Perhaps you could you use a subsearch to find the data you are interested in | fields x and then pass those to the outer search?