Best way to visualize number of distinct tags over time?

InfluxDB 2 Dashboards
, ,
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.

Updated for clarity
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 00C5
1608896451000000000 90 39.35 62 101.19 00C6
1608896451000000000 90 38.62 11 102.13 00C5
1608896451000000000 90 41.15 37 101.28 00Cb

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

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

I have three related questions:

  1. What is the best query to use to determine the number of distinct tags in a database?
  2. Is is possible to query the number of tags that have reported data within a given time range?
  3. Is it possible to create a chart in Grafana that shows how many distinct tags are reporting data over time?

All suggestions welcome.

2

Hello @NickN,
Are you using 1.x or 2.0?
First I’d like to recommend against making a tag with unbounded values a tag, that should be a field instead. Things sensor ID, customer ID, etc are typically made fields to reduce cardinality and avoid runaway cardinality.

If you’re using 2.0, I recommend using the operation monitoring template to track your cardinality.

3

Hi @Anaisdg

I’m using 1.8 as it’s running on 32-bit Raspbian OS.

I’m curious, and forgive me if this is an utterly noob question, but what is a good example use case for tags?

Sensor ID seemed to make sense as we often want to search on everything from a specific sensor, but I may well have misunderstood.

Thanks for your help!

4

Hello @NickN,
SensorID can be a tag if it’s scoped. However, if you’re planning to grow your sensors indefinitely, then making sensorID a tag might result in runaway series cardinality.

No problem, that’s what we’re here for. Does that help?

5

My apologies! I didn’t get a notification when you replied. And yes, your answer was very helpful.

There would definitely be an upper limit to the number of sensors. I would estimate no more than 300 maximum.

6
  1. What is the best query to use to determine the number of distinct tags in a database?
  2. Is it possible to query the number of tags that have reported data within a given time range?
  3. Is it possible to create a chart in Grafana that shows how many distinct tags are reporting data over time?
  1. To show all the existing values for a tag you can use:
    SHOW TAG VALUES WITH KEY = "__YourTagName__"
    To have a count of the values you can use
    SHOW TAG VALUES CARDINALITY WITH KEY = "tag" (estimated, exact is also available, see docs)
    The result returned sometimes is weird and cannot be visualized, so try some combinations and see if it’s actually usable (in the influx-cli the result will be readable so you can check it there for reference)

The other 2 points are a bit tricky as they require subqueries

  • to SELECT points, at least a field must be in the select list. Therefore even if we don’t need any field, at least one must be returned.
    if the field has no value no point will be returned, therefore choose a field that is always valued, or just use SELECT *
  • COUNT DISTINCT cannot be used directly on tags, therefore a subquery is needed.

This sample allows you to have the “tag” count over time

SELECT COUNT(DISTINCT "tag") as "TagCount"
FROM (
	SELECT "time","tag","ValueA"
	FROM "logins.count"
	WHERE $timeFilter
)
GROUP BY time($__interval)

In order to have the total count for a defined time range as a KPI or similar, the query stays the same, ideally, you should remove the GROUP BY… but that can’t be done as that’s required by the COUNT function.
therefore I suggest you put a really big time interval, like “1d” maybe in order to group all the points in the same “range bucket”.
Note that KPIs (“Stats” chart) will force you to also select another aggregation function (Display → Calculation) on top of the returned result like Last, Max, etc.
which is not a problem as long as you just have a one-point result, but might be not accurate if you have more points so pay attention

2 Likes