Stable database queries using SQL for Grafana integration

I’d like to create complex dashboards using Grafana while allowing users to update database entries using Baserow. For example, we have a booking system in Baserow that allows people to schedule time against equipment in our lab.

The lab contains networked equipment that can sometimes be unplugged or go out for refurbishment.

I created a read-only, authentication-free Grafana dashboard that queried the table and it worked up until a row was modified to have two values in the equipment column:

The dashboard broke because the equipment column is no longer directly associated with the equipment_addresses table:

We have a separate dashboard that doesn’t include the equipment, which works fine because it isn’t performing a join with the equipment column:

Hopefully it’s apparent that we’d like to list the IP address, equipment name, equipment type, and equipment location, for each piece of networked equipment. Ideally, through Grafana to get dashboard integration without authentication.

Here are some relevant tables names and values:

  • database_table_433 – Contains the IP address (field_3895) and the online flag (field_4958). This used to contain the equipment reference, but has since moved.
  • database_table_426 – Contains the equipment name (field_3866).
  • database_table_425 – Contains the equipment type (field_3860).
  • database_field – For table_id 433, the relevant rows contain address (id 3895, content_type_id 1), equipment (id 3897, content_type_id 41), and active (id 4958, content_type_id 36).

How would you write a stable SQL query against the Baserow PostgreSQL database that JOINs the data from the equipment table to the equipment_addresses table? Ideally, I’d like to create either a materialized view or regular database view that Grafana can query.

Preferably, this could use a PostgreSQL function to retrieve the data so that we could limit (constrain) the relation between an address and network equipment to be 1:1. Possibly resembling the Python API calls or similar.

For the moment, knowing where to find the relationship between the equipment and equipment_addresses would be fantastic. It’s not clear to me where those relations are stored.

The database_linkrowfield table contains the relation between the field (3897) and the table id (426). Specifically, the link_row_relation_id column contains the value 455, which can then be used to query the corresponding relation table.