> How can I fix this query? The query isn't broken, it's returning exactly what you are asking it to return. If server 2 doesn't send any data for a 10s time period then it won't be in the query returns. Server 2 should have a data point at "2016-08-18T13:23:33Z" if it is reporting every 10 seconds. That would be included in the "2016-08-18T13:23:30Z" SUM() bucket if it existed.
> I'd need a fill(next) or some way to cut off the first and last row of the query result. > Is there a way to do this? Ideally without the need of an helper continuous query, because I'll need more queries of this kind (aggregates over multiple servers). Since all the buckets except the first and the last are correct, why not just expand your query range to include 10s extra at the beginning and the end, and then throw away those buckets? On Tue, Aug 23, 2016 at 3:32 AM, Andreas Gerstmayr < [email protected]> wrote: > Hello! > > I'd like to display cumulative network statistics over all servers. Each > server sends every 10s the network counter value to the InfluxDB (with > collectd), but not necessary synchronized (e.g. server A sends at :00, :10, > :20, ... and server B at :03, :13, :23, ...): > > > SELECT sum("value") FROM "interface_rx" WHERE "type" = 'if_octets' AND > "instance" = 'bond0' AND time > 1471526615s and time < 1471526650s GROUP BY > host, time(1s) > name: interface_rx > tags: host=server2 > time sum > ---- --- > 2016-08-18T13:23:35Z > 2016-08-18T13:23:36Z > 2016-08-18T13:23:37Z > 2016-08-18T13:23:38Z > 2016-08-18T13:23:39Z > 2016-08-18T13:23:40Z > 2016-08-18T13:23:41Z > 2016-08-18T13:23:42Z > 2016-08-18T13:23:43Z 3.3923148321379e+13 > 2016-08-18T13:23:44Z > 2016-08-18T13:23:45Z > 2016-08-18T13:23:46Z > 2016-08-18T13:23:47Z > 2016-08-18T13:23:48Z > 2016-08-18T13:23:49Z > 2016-08-18T13:23:50Z > [...] > > name: interface_rx > tags: host=server4 > time sum > ---- --- > 2016-08-18T13:23:35Z > 2016-08-18T13:23:36Z > 2016-08-18T13:23:37Z 3.4499124855018e+13 > 2016-08-18T13:23:38Z > 2016-08-18T13:23:39Z > 2016-08-18T13:23:40Z > 2016-08-18T13:23:41Z > 2016-08-18T13:23:42Z > 2016-08-18T13:23:43Z > 2016-08-18T13:23:44Z > 2016-08-18T13:23:45Z > 2016-08-18T13:23:46Z > 2016-08-18T13:23:47Z 3.4500398715445e+13 > 2016-08-18T13:23:48Z > 2016-08-18T13:23:49Z > 2016-08-18T13:23:50Z > [...] > > > When I group them over a timespan of 10s, I get: > > > SELECT sum("value") FROM "interface_rx" WHERE "type" = 'if_octets' AND > "instance" = 'bond0' AND time > 1471526615s and time < 1471526650s GROUP BY > host, time(10s) > name: interface_rx > tags: host=server2 > time sum > ---- --- > 2016-08-18T13:23:30Z > 2016-08-18T13:23:40Z 3.3923148321379e+13 > 2016-08-18T13:23:50Z 3.392398043915e+13 > 2016-08-18T13:24:00Z 3.3924586196425e+13 > > name: interface_rx > tags: host=server4 > time sum > ---- --- > 2016-08-18T13:23:30Z 3.4499124855018e+13 > 2016-08-18T13:23:40Z 3.4500398715445e+13 > 2016-08-18T13:23:50Z 3.4501449067387e+13 > 2016-08-18T13:24:00Z 3.4502012732448e+13 > > > In the timespan from 2016-08-18T13:23:35Z to 2016-08-18T13:23:40Z server2 > has no datapoint, so the value is empty. > If I aggregate it now over all hosts, I get a wrong value for the first > row (because server2 has no datapoint): > > > SELECT sum("value") FROM "interface_rx" WHERE "type" = 'if_octets' AND > "instance" = 'bond0' AND time > 1471526615s and time < 1471526650s GROUP BY > time(10s) > name: interface_rx > ------------------ > time sum > 2016-08-18T13:23:30Z 3.4716782207702e+13 > 2016-08-18T13:23:40Z 8.7242343850004e+13 > 2016-08-18T13:23:50Z 8.7244729225214e+13 > 2016-08-18T13:24:00Z 8.7247043118781e+13 > > (Values don't add up because I omitted some servers to keep this post > short) > > > How can I fix this query? > I'd need a fill(next) or some way to cut off the first and last row of the > query result. > Is there a way to do this? Ideally without the need of an helper > continuous query, because I'll need more queries of this kind (aggregates > over multiple servers). > > > Versions: > InfluxDB 0.13.0 > Grafana 3.1.1 > > -- > 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/101b32b3-be05-47a8-8ef2-16d0c445faf3%40googlegroups.com > <https://groups.google.com/d/msgid/influxdb/101b32b3-be05-47a8-8ef2-16d0c445faf3%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/CALGqCvPX0xagLRL8mOfTMh%3DE-n4DS-UAZ0H-GD_ia-tME9SH_g%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
