0

I have a grafana dashboard where I want to use an orcestra cities map dashboard to show status of some stations. The status is available as json from a http server (using nagios for this part) but the status has no idea of the location of the stations. This I have in a postGIS database.

I know I can set up a script that reads the status json and inserts the data into a table in the postgis database. This can run each five minutes or something. This feels a bit kludgy, so I wonder if there are some other ways of doing this.

Could it be possible to use a foreign data wrapper to fetch the json into postgis? The only json fdw I have found is to read a set of files, I would need to read from a http server.

If not, is it possible to combine data from json and postgres in one data set in grafana? I can read in data from both sources and present them e.g. as time series in one panel, but here I need to be able to join the two so that I use some of the attributes from json to categorize the points from postgis (or the other way around if that should be easier)

MortenSickel
  • 2,118
  • 4
  • 26
  • 44

1 Answers1

1

In theory you can do that in the Grafana. You need to have 2 queries with results from both sources (how to write query, configure datasources for that is not in the scope of this question) + you need a key, which can be used for a join in both results (e.g. city_id).

Then you may use join transformation to "join" both query results into single dataset.

Jan Garaj
  • 25,598
  • 3
  • 38
  • 59
  • Thanks, that works. I just had to make sure that both the layers had the same name of the field to join. The status field that I wanted to use for symbolizing came up twice "status A" and "status (base field name)" When using the latter for colors, it worked. What was a pity is that it is not possible to display other layers when I have done a transformation - the select drop down that used to contain the names of the queries is empty and I cannot type into it. – MortenSickel Jul 06 '22 at 10:26