I'm not sure of the proper approach for this query. I have a list of events,one event per day, with fields min,max and base. Starting with any date I need to pull the base for that date as well as the highest Max in a range span of 3 or 4 events [Note: I say events because because the log has missing dates].
as an Example snippet:
Date,Min,Max,Base
2014-07-23,8.66,8.79,8.59
2014-07-22,8.68,8.74,8.58
2014-07-21,8.59,8.69,8.49
2014-07-18,8.59,8.73,8.45
I would need to return the date, its base and the highest Max in the range:
Date, Base, Max
2014-07-18, 8.45, 8.68
For creating the ranges, should I should use a span or a bin with a foreach or is there a more appropriate method?
Not sure if I understand your requirements correctly... here's what I think you want:
Since there's no way or knowing how far in the future you need to look to get the next three events you could do something like this run over a timerange from the specified date to now:
your base search | tail 4 | stats earliest(Date) as Date earliest(Base) as Base max(Max) as Max
If your specified date is far into the past and there's a lot of data since then this would churn through all of the data and discard most of them - so if possible you can specify a latest time closer to the specified date but far away enough to be sure there will be another three events in there.
Try something like this (assuming the fields are already extracted by same name.
your base search | streamstats count | eval count=floor((count-1)/4) | stats first(Date) as Date first(Base) as Base max(Max) as Max by count
where '4' is the bin size.
No - I want to group a number of Events together and get the Base of the first event in the group and the get the highest Max for the group.
You want to group fix no of EVENTS together and get the Base and Max value for the lowest date?? (base on the sample output that you provided)