Chart number of distinct tags over time / Influx + Grafana

Grafana
1

Hello All,

I have a network of sensors passing data to influxdb. Each sensor has a unique ID, used as a tag in Influx.

Let’s say we start with 100 sensors (and therefore 100 different tags). Over time, some sensors get damaged, unplugged or otherwise incapacitated.

What I would like to do is show a chart over time of how many distinct tags are reporting data.

I’m relatively new to Grafana, but I’ve been able to solve most of the other (self created) problems I’ve run into. However, I’m totally stuck on how to approach this one.

All suggestions welcome.

2

Why you are creating new tag for each sensor and not just tag value? It will help if you show example of your DB structure, because it looks like inefficient InfluxDB design.

3

Thanks for your reply. My apologies. I’m new to Influx and I realize I misused the term.

Let me be more precise.

I have one tag key defined (confusingly called “tag”) and each sensor ID is a tag value.

Here’s some sample data that shows the structure:

time Battery ValueA ValueB ValueC tag

1608896450000000000 90 38.62 60 101.19 5
1608896451000000000 90 39.35 62 101.19 6
1608896451000000000 90 38.62 11 102.13 5
1608896451000000000 90 41.15 37 101.28 b

If I’ve understood the terminology properly, the sensor IDs are tag values and the rest are measurements.

Hopefully that makes more sense?

4

You need subqueries (keywords to google and learn - influxdb subquery). Inner query will return aggregated values per tag and outer query count inner results. Example - not a copy paste query - syntax can be wrong - it is just to give you idea:

SELECT COUNT(mean)
FROM (
  SELECT MEAN("field") 
  FROM "measurement" 
  WHERE $timeFilter
  GROUP BY time($__interval), "tag"
) 
GROUP BY time($__interval)
5

Thank you!

That’s exactly the kind of pointer I was looking for.

6

Quick update: it works like a charm.

Here’s the exact query I used in Grafana.

SELECT COUNT(mean)
FROM (
SELECT mean("myField") FROM "myMeasurements" WHERE $timeFilter GROUP BY time($__interval), "tag" 
)
GROUP BY  time($__interval)

And here’s the resulting chart:

image
image753×301 14.8 KB

Thanks again for the help!

1 Like
7

…question moved one level up…

8

Hi, I suppose I am even newer to Grafana but do have a similar problem. Your help would be greatly appreciated. I have a table with a single field :“MAC” and the time of course. Now all I want to query is the number of distinct MACs within a certain time-intervall. I suppose that also requires the nested query but I can’t figure out how it is done.
Cheers
fields

9

In InfluxQL, DISTINCT() returns the unique values of a single field.

Assuming you are using InfluxQL (not Flux), and assuming your measurement name is called ESP8266_01, maybe this?

SELECT COUNT(DISTINCT("MAC")) FROM "ESP8266_01"
10

This topic was automatically closed after 365 days. New replies are no longer allowed.