Sending room information to Influxdb

[lm1]

Is it possible to have the influxdb logger send the room that a devices is assigned to along with all of the other data. It would be useful in creating dashboards with Grafana. I want to graph all of the temp/humidity sensors in a greenhouse but will be adding more and don't want to have to keep modifying a where clause in the query in Grafana.

Thanks in advance.

[mavrrick58]

Unlikely. It looks like when reviewing documentation that the Rooms is simply a UI tool for given devices. It may not be retrieveable by apps.

[Alan_F]

This response may be of limited use to you if you're using InfluxDB 1.8 and InfluxQL, but maybe the way I solved this will give you an idea for a way to do something similar if you're using InfluxDB 2/Flux.

TLDR: Using MySQL as my logging database and Node Red to populate a devices table, I'm able to use rooms and device types in my Hubitat Logs Grafana dashboard.

I send my Hubitat logs to MySQL (using a variation of a Node Red flow found on this forum) and just started using Grafana to view them. I'm using a separate "devices" table that contains all the devices and apps, and each device's room is listed there, so I can use the rooms in Grafana by joining the logs table to the devices table.

My main motivation for having a devices table was to have a name for each device that didn't include markups, for example how Mode Manger might be "Mode Manager Night" or "Mode Manger Away" in the logs. Actually those look more like "Mode Manager Night" in my logs, which is even worse.

I use two database functions to strip the HTML tags from the logs where they appear in the device names and messages. The first function unencodes the HTML special characters in the tags (so < becomes < and > becomes >), and the second strips the tags from the device names (so "Mode Manager Away" becomes just "Mode Manager Away". Using both of those functions while trying to pull the distinct list of device names directly from the logs was running way too slow on my Raspberry Pi-hosted MySQL server, and it still left me with "Mode Manager Away" and "Mode Manager Night" and "Mode Manager Day" as 3 different names for the same app.

So both to speed things up and to have just one device/app name per device/app, I needed the separate table.

Using Node Red I periodically run a flow that pulls all the devices from MakerAPI via HTTP request. It then splits the response into one message per device, and writes the results into my "devices" table. The fields I get from MakerAPI are the device id, name, label, type, and room. This means I can also use the device types in my Grafana filter drop-downs, which I find very useful.

Since MakerAPI only returns the devices and I also want to see apps in my 'device' list in the logs, I run a second query to pull all the app names directly from the last day's log entries (after initially running it one time on the full log history to get all app names). Since apps don't have rooms, I just insert "N/A" for the room for each app:

REPLACE INTO devices (id,name,label,type,room) select t2.id, t2.name, t2.name,'app','N/A' from (select name, id, max(time) from logs where type = 'app' and date >= now() - interval 1 day group by id) t2;

In my Grafana dashboard I have these filters:

I have drop-downs for both devices/device types to include and devices/device types to exclude. I exclude a bunch of chatty device types by default.

[lm1]

I figured out another method. In the new version of the Inflluxdb logger you can have multiple instances. With that I have a bucket in influxdb for each room and a seperate logger for each room directing it to that bucket. So far so good.