Aggregations grouped by value and time to generate square wave style graph


(Craig Castle-Mead) #1

I've had a good look around for options on this, but I'm either missing something, or, the keywords I've been used are relatively generic and getting lots of other search results for that aren't giving me what I need (or, it's just not possible).

I have data samples coming into elasticsearch in 5 minute intervals. One of the values I need to monitor is a mostly consistent integer, but every few months may change up or down and I need to report on this change at the exact timestamp. The value may then revert back at later date (or change again)

While traditional aggregations will show me the total count of all values, what I'm ideally looking for is a bucket with the start/end date for each time range. As I need to know the exact sample when it changed, bucketing by month/week/day and then sub-bucketing by value will at least show me when a value changed, but I'd then not have the resolution I needed without a second call, and with > 8500 samples/month and years of data, requesting and rolling through each row is an expensive operation

Example:

Assume the value was 5 from Jan 1, 2016 00:00 till June 5th 14:00 when it changed to 3, then back to 5 on October 1 4:00 and stayed there until today, I'd ideally be looking for a response like (pseudo):

[
dateStart: 2016-01-01 00:00:00,
dateEnd: 2016-06-05 14:00:00,
value: 5
],
[
dateStart: 2016-06-05 14:05:00,
dateEnd: 2016-10-01 04:00:00,
value: 3
],
[
dateStart: 2016-10-01 04:05:00,
dateEnd: 2017-08-05 15:00:00,
value: 5
]

Any pointers would be greatly appreciated. The closest I can think of at the moment is bucketing by month then value, seeing what months have > 1 sub bucket, and then drilling down again, but this seems like it'd be a commonly used function

CCM


(system) #2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.