InfluxDB - one table with two metrics

Hi,

I want to have a table showing information like this:
User Status Time
John Armed 26.05.2017 19:27:39
Kate Disarmed 26.05.2017 18:20:30
John Armed 25.05.2017 19:27:39

The first column is captured in table “Tex_Alarm_User”, the second column is captured in table “Tex_Alarm_Status”. The last column is a timestamp when the entries were changed.

This is what I have so far:

It shows the timestamp and the alarm status:

How do I add a second column to this overview?
I tried adding a new metric and selected table “Tex_Alarm_User”, but nothing was added to the table displayed.

I think one of the issues is maybe that I want information from two tables that are technically not linked to each other (no shared key field). The timestamp in each table is “practically” the same (milliseconds difference), but maybe we can do a select in table 2 with the timestamp of table 1?

I’m pretty new with Grafana, so my apologies if my question is considered to be a dumb one. A search on the forum did not produce any answers for me.

Thank you very much for your help!

Dries

  1. Select multiple tables using regex. Here I have included the cpu and derivative tables:
  2. To add multiple fields, click the plus button and choose Fields → field:
1 Like

Hi @daniellee,

Thank you for your help! I didn’t realise you could use REGEX in that field.

This is what I currently have:

Which results in this table:

Some things I don’t understand:

  • I now see additional columns, but both of them have the same values?
  • Why does it sometimes say “Tri”? Both the user and status are numerical values.
  • By default, Grafana uses “mean()”, but could it also display the value “as is”? I don’t want the mean.
  • Where can I rename the column headers (here mean and mean_1)? I see you have your columns given a meaningfull name.

Thanks!

Dries

  1. You have the same field twice. At the moment, you have a line per table (Tex_alarm_Status and Tex_Alarm_user). If you want to merge them into one line then I don’t think that is possible but I’m not an InfluxDB expert. If you format as Time series there are a lot of options for aliasing (and InfluxDB is usually returned as time series data) but when formatting as table data there are not so many options for aliasing.
  2. Tri stands for trillion (rather than showing a lot of zeros). Mil for million and Bil for billion.
  3. You are grouping the values together by time interval so you need to have some way to aggregate multiple values. Remove the group by altogether if you do not want to group them at all.
  4. Use alias to name your fields to more meaningful names.

Thanks again for your quick reply. I’m starting to understand. :slight_smile:

I adjusted to this:

Which results in this:

Both “user” and “status” are now shown in the table, which is good.
Unfortunately, also table “Tex_Alarm_Status_Update” is added. Is there a way to exclude it?

Also, as soon as I use a “Group by” option, the table is shown as empty…

  • For the regex, surround the values with ^ and $. ^means start of the word and $ is the end.
  • What did you group by?

Thanks again, I am getting there. :slight_smile:

I adjusted to this:

Which results in this:

Since I now group by time(5s), the first column shows the same timestamp for both “user” and “status”. Is it now possible to show the information in one row (two columns)? So something like this:
Date - User - Status
2017-05-28 22:34:10 - 9.00 - 2.00

Thanks,
Dries

For grouping by time, I would use GROUP BY time($__interval) and set the Group by time interval field to >5s. This sets the minimum grouping to be at least 5 seconds but will automatically aggregate by larger values when you zoom out (if you zoom out to look at the last 3 months then you do not need to group by 5 seconds intervals).

Merging two tables as I mentioned in a previous answer above is hard in InfluxDB. I don’t know the answer to that. Maybe worth asking on the InfluxDB community site or looking at their documentation. I think the answer you will get is to redesign your tables into one table.

Hi,
I got a bit stranded on the InfluxDB community. The key problem is that the timestamps of the two tables are not exactly the same. So there is no common key.

I was thinking of a different approach.
I can merge the information in one table before I save to Influx DB, I could use a separator (e.g. “;”).
Records would display something like:
John;Armed
Kate;Disarmed
John;Armed

Would it be possible to split these records back in to separated columns based on the separator?