The other day I ran into a limitation of Influxdb with Grafana. I have an Influxdb that I feed with data from my ADS-B receiver. This feed contains data like
Reg: PH-AOA
Type: A332
What I wanted to do is count the number of UNIQUE Airbus A330-203’s.<\/p>\n
TEN hits<\/strong> on PH-AOE<\/strong> A332 must be counted as\u00a0ONE A320-203<\/strong> and NOT ten!<\/strong><\/p>\n To do that you need to create a query with “GROUP BY”. Easy enough. Now here comes the problem: You cannot do a “GROUP BY” in Influxdb that connect two values. You can only do that with a TAG.<\/span><\/p>\n The query looks like this:<\/p>\n SELECT count(distinct(“Reg”)) FROM “mesg” WHERE $timeFilter GROUP BY time(1d), “actype”<\/p>\n This tag “actype” does not exist in the datafeed so it must be created. I already use node red to collect and organise the feed so it makes sense to to it there.<\/p> The ADS-B receiver on the Pi produces the above JSON-feed that after a bit of tinkering looks like this:<\/p> <\/p> msg.payload=[msg.payload,{tag:value}];<\/p>\n <\/p>\n To add two tags:<\/p>\n Do this:<\/p>\n msg.payload=[msg.payload,{icao24:msg.payload.Icao},{actype:msg.payload.Mdl}]; <\/p>\n SELECT count(distinct(“Reg”)) FROM “mesg” WHERE $timeFilter GROUP BY time(1d), “actype”<\/p> The result is an aggregated list of aircraft types from unique registrations over a period of 1 day.<\/p>\n\n\n\n\n\n\n\n\n\n","protected":false},"excerpt":{"rendered":" Dit maakt het mogelijk om voorkomens van gecombineerde gegevens in Grafana weer te geven. Een vitale stap voor het tellen van de vliegtuigtypes in het mode-S project.<\/p>","protected":false},"author":1,"featured_media":1313,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[34,9,33,20,28],"tags":[42,44,43],"class_list":["post-1269","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-adsb","category-airplanes","category-avionics","category-internet","category-radio","tag-grafana","tag-influxdb","tag-nodered"],"_links":{"self":[{"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/posts\/1269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/comments?post=1269"}],"version-history":[{"count":33,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/posts\/1269\/revisions"}],"predecessor-version":[{"id":2064,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/posts\/1269\/revisions\/2064"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/media\/1313"}],"wp:attachment":[{"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/media?parent=1269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/categories?post=1269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rent-a-pilot.nl\/nl\/wp-json\/wp\/v2\/tags?post=1269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Influxdb query – group by tag: actype<\/h3>\n
We need a TAG to group these entries<\/h3>\n
Node-Red to the rescue<\/h2>\n
How to add a TAG-value for Influxdb with a Node-Red function node<\/h3>\n
Example<\/h4>\n
\n
return msg<\/p>\nYou can now do this query in Grafana:<\/h3>\n