Rent-a-Pilot Aeronautics

airplanes and other distractions

Counting Stuff in Grafana – How to use Node-Red to make the “Group by TAG” useful in Influxdb

Dec 4, 2020 | ADSB, Airplanes, Avionics, Internet, Radio

ADS-B Grafana: Make lists of  types, calsigns and more

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.

The end goal

TEN hits on PH-AOE A332 must be counted as ONE A320-203 and NOT ten!

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.

Influxdb query - group by tag: actype

The query looks like this:

SELECT count(distinct("Reg")) FROM "mesg" WHERE $timeFilter GROUP BY time(1d), "actype"

We need a TAG to group these entries

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.

Node-Red to the rescue

The ADS-B receiver on the Pi produces the above JSON-feed that after a bit of tinkering looks like this:

How to add a TAG-value for Influxdb with a Node-Red function node

 

Syntax

msg.payload=[msg.payload,{tag:value}];

 

Example

To add two tags:

  • Add this tag "icao24" from value "msg.payload.Icao"
  • Add this tag "actype" from value  "msg.payload.Mdl"

Do this:

msg.payload=[msg.payload,{icao24:msg.payload.Icao},{actype:msg.payload.Mdl}];
return msg

 

You can now do this query in Grafana:

SELECT count(distinct("Reg")) FROM "mesg" WHERE $timeFilter GROUP BY time(1d), "actype"

The result is an aggregated list of aircraft types from unique registrations over a period of 1 day.

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

Translate you want?


Your wish you get.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

0
Would love your thoughts, please comment.x
()
x