CODEWITHSUNDEEP
influxdbgrafana
Eric Goncalves
Eric Goncalves

Reputation: 5353

Live sum(value) data visualization in Grafana using InfluxQL

I have a table that has many rows that follow this structure:

time                acknowledgement crit current_attempt downtime_depth execution_time hostname latency max_check_attempts metric reachable service state state_type value warn
----                --------------- ---- --------------- -------------- -------------- -------- ------- ------------------ ------ --------- ------- ----- ---------- ----- ----
1507171563000000000                 999                                                a01-pdu                             kw               pdu                      14.94 999
1507171621000000000                 999                                                a01-pdu                             kw               pdu                      14.92 999
1507171678000000000                 999                                                a01-pdu                             kw               pdu                      14.45 999
1507171736000000000                 999                                                a01-pdu                             kw               pdu                      14.93 999
1507171793000000000                 999                                                a01-pdu                             kw               pdu                      14.88 999
1507171852000000000                 999                                                a01-pdu                             kw               pdu                      14.92 999
1507171909000000000                 999                                                a01-pdu                             kw               pdu                      14.9  999
1507171995000000000                 999                                                a01-pdu                             kw               pdu                      14.91 999
1507171999000000000                 999                                                a02-pdu                             kw               pdu                      13.41 999

My goal is to get the last entry where hostname =~ /a01-pdu|a02-pdu/ and sum their value column

For exammple if I run: 

SELECT last(value) AS last INTO pdu_abcd12 FROM pdu WHERE hostname =~ /a01-pdu/;
SELECT last(value) AS last INTO pdu_abcd12 FROM pdu WHERE hostname =~ /a02-pdu/;

SELECT sum(last) FROM (SELECT * FROM pdu_abcd12 ORDER BY DESC LIMIT 8)

time sum
---- ---
0    105.97

I am able to get the sum of those two values, but that requires me to run the influxQL commands above every time I want to see the data. Is there a way to have the commands run periodically so I can visualize the live data in Grafana?

Upvotes: 0

Views: 415

Answers (1)

danny
danny

Reputation: 5270

Create continuous queries, for example:

CREATE CONTINUOUS QUERY last_sum ON <db name>
BEGIN
  SELECT last(value) AS last INTO pdu_abcd12 
  FROM pdu 
  WHERE hostname =~ /a01-pdu/ 
  GROUP BY time(1h), hostname
END

Note that CQs require a group by. Hostname is also included in group by above to account for multiple hostnames being matched by the regex. Their values would otherwise be selected together.

There are, however, several drawbacks to CQs, such as they are periodic meaning they only run once within the group by interval, they will not contain intermediate data between intervals, will not run if the DB is down leading to gaps in data, will generate unique fields when wildcard aggregations are used and so forth, see documentation.

Another option is Kapacitor and its more flexible, but also more involved, stream processing syntax.

Upvotes: 0

Related Questions