After editing these two files run the command
systemctl restart postgresql
to enable your changes. Now running the command
psql -U postgres -h untangle
should work (as long as you use the correct hostname/IP address after -h).
From the Grafana web interface, choose Configuration > Data Sources > Add Data Source > PostgreSQL Give your data source a name, type the IP address of your host and :5432 in the Host field, type uvm for the Database, postgres for the User and select your PostgreSQL version from the Version drop down then click Save & Test. If you are unsure of the version, you can type this command into psql:
uvm=# select version(); version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 11.11 (Debian 11.11-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 row)
So, even though Grafana happily connected to the PostgreSQL on the Untangle appliance, I could not get any tables to show up in my Grafana panels. (I have a feeling that Grafana does not like PostgreSQL schemas which Untangle uses heavily. You can find the full Untangle PostgreSQL schema/table layout here). Through failing, and failing, and failing, and succeeding, I learned that I needed to manually enter the schema.tablename into the From field of the new Grafana Panel. Once I did that, all the other fields became populated. In the example above, I wanted to graph intrusion_prevention_events, so I typed reports.intrusion_prevention_events. reports is the schema in this case.
My initial goal was to duplicate the table of blocked intrusion prevention events from the Untangle dashboard. Unfortunately, Grafana picked up the MSG field as a metric to be filtered on and did not pick up source_ip and destination_ip at all. I’m sure if I had more experience with Grafana <> PostgreSQL I might be able to fix that, but I moved on. I decided to create a line graph of number of events rather than a table of events. If the graph spiked in Grafana, I could drill down in the Untangle interface for specifics. I eventually landed on graphing a count of events over 5 minute intervals.
My next problem to solve was that my graph time on the X axis was 4 hours behind. Very odd as my Grafana Server, my Untangle Appliance, and my laptop are all set to the same time and time zone. Who knows? After a little Googling, I learned that I could shift time in a PostgreSQL statement with “time_stamp + interval ‘4’ hour”. I added this format to the SELECT and the WHERE and Bob’s Your Uncle, the graph lined up with reality. I’m open to suggestions as to why this might have happened.
Well, that’s about it. Open up your PostgreSQL database to other hosts, add as a Grafana source, and type in the name of the table preceded by the schema name. Thank you for taking the time to read this post. I hope you found the post helpful and/or informative. I welcome your feedback.