This is also something that can be done using Kapacitor.
Here is an example TICKscript to do what you want using a basic mean
aggregate operation.
batch
|query('SELECT mean(usage_user) FROM telegraf."default".cpu')
.period(1d)
.every(1m)
.align()
|influxDBOut()
.database('db')
.retentionPolicy('rp')
.measurement('m')
.precision('s')
If you need to do this for multiple measurements then you can use a
template task, to define the measurement names using vars and define a task
per measurement.
On Thursday, June 16, 2016 at 4:17:34 PM UTC-6, Sean Beckett wrote:
>
> Excellent workaround! That's effectively the same as my proposed CLI
> solution but you've automated it in a more maintainable way.
>
> On Thu, Jun 16, 2016 at 3:23 PM, <[email protected] <javascript:>> wrote:
>
>> Sean - thanks so much for this reply, super helpful!
>>
>> For the time being we've gone with an AWS Lambda that fires every minute
>> and executes a query with an INTO clause, simulating a CQ. Works pretty
>> well so far!
>>
>> Thanks again,
>> -Avery
>>
>> On Thursday, June 16, 2016 at 4:48:23 PM UTC-4, Sean Beckett wrote:
>>>
>>> That is correct. RESAMPLE EVERY 1m ... GROUP BY time(24h) means every
>>> minute recalculate the data for the 24 hours that start at midnight and
>>> include today.
>>>
>>> What you want, which is to calculate the 24h query every minute for the
>>> previous 24h -- NOT the previous calendar day -- is not supported by a
>>> single CQ. The system automatically buckets on round number boundaries and
>>> they are not relative to the time at which the query is run.
>>>
>>> There is an offset to shift the bucket boundaries
>>> <https://docs.influxdata.com/influxdb/v0.13/query_language/data_exploration/#group-by-time-intervals>,
>>>
>>> but it, too, is absolute, not relative.
>>>
>>> One way to accomplish what you want right now is with 1440 configured
>>> CQs, each running every minute. It's a heavy load on the system and a poor
>>> workaround, but it will work. It should be easy to script their creation,
>>> but basically you want something like this (omitting the syntax that
>>> doesn't matter):
>>>
>>> CREATE CQ foo1 RESAMPLE EVERY 1m BEGIN
>>> SELECT ... GROUP BY time(24h)
>>> END
>>>
>>> CREATE CQ foo2 RESAMPLE EVERY 1m BEGIN
>>> SELECT ... GROUP BY time(24h, 1m)
>>> END
>>>
>>> CREATE CQ foo3 RESAMPLE EVERY 1m BEGIN
>>> SELECT ... GROUP BY time(24h, 2m)
>>> END
>>>
>>> ...
>>>
>>> CREATE CQ foo1440 RESAMPLE EVERY 1m BEGIN
>>> SELECT ... GROUP BY time(24h, 1439m)
>>> END
>>>
>>> Every minute, the system will recalculate the 24h value for all 1440
>>> possible 24h buckets.
>>>
>>> Another way to solve the problem would be to automate an ad hoc query.
>>> Use the -execute
>>> <https://docs.influxdata.com/influxdb/v0.13/tools/shell/#execute-an-influxql-command-and-quit-with-execute>
>>>
>>> flag for the CLI and submit, every minute, the appropriate query: SELECT
>>> ... GROUP BY time(24h, Xm), where X would monotonically increase to
>>> 1439 until resetting to 0, each day.
>>>
>>> On Tue, Jun 14, 2016 at 1:10 PM, Mathias Herberts <[email protected]
>>> > wrote:
>>>
>>>> IIRC CQs are evaluated on intervals which are separated by the 'GROUP
>>>> BY' timespan and fall on round boundaries, so basically you are
>>>> recomputing
>>>> every minute the data for the current 24hour interval.
>>>>
>>>> On Tuesday, June 14, 2016 at 8:21:10 PM UTC+2, [email protected] wrote:
>>>>>
>>>>> Hey Folks,
>>>>>
>>>>> We are trying to run a CQ each minute which calculates a bunch of
>>>>> aggregates for the last 24 hours on a sliding scale. We are using:
>>>>>
>>>>> RESAMPLE EVERY 1m ....... GROUP BY time(24h)
>>>>>
>>>>>
>>>>> The problem is we are getting our resulting time stamps as
>>>>> "2016-06-14T00:00:00Z"
>>>>> (for example) which gives us one measurement *per day* instead of one
>>>>> measurement *per minute* for the last 24 hours.
>>>>>
>>>>>
>>>>> Would appreciate any direction on how to do this correctly!
>>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> -Avery
>>>>>
>>>> --
>>>> Remember to include the InfluxDB version number with all issue reports
>>>> ---
>>>> You received this message because you are subscribed to the Google
>>>> Groups "InfluxDB" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to [email protected].
>>>> To post to this group, send email to [email protected].
>>>> Visit this group at https://groups.google.com/group/influxdb.
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/influxdb/58185324-0bd5-44d6-999f-c33b0101d4f7%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/influxdb/58185324-0bd5-44d6-999f-c33b0101d4f7%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>>
>>>
>>> --
>>> Sean Beckett
>>> Director of Support and Professional Services
>>> InfluxDB
>>>
>> --
>> Remember to include the InfluxDB version number with all issue reports
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "InfluxDB" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/influxdb.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/influxdb/2f77ec44-4d3e-4d95-a666-2bf6e8d5e65c%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/influxdb/2f77ec44-4d3e-4d95-a666-2bf6e8d5e65c%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> Sean Beckett
> Director of Support and Professional Services
> InfluxDB
>
--
Remember to include the InfluxDB version number with all issue reports
---
You received this message because you are subscribed to the Google Groups
"InfluxDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/influxdb.
To view this discussion on the web visit
https://groups.google.com/d/msgid/influxdb/38c6693d-b610-4289-9c98-4cc4b776fcea%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
