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

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

 

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
0
Would love your thoughts, please comment.x
()
x