Differences

This shows you the differences between two versions of the page.

--- graficar_variables_de_ups_en_grafana [2024/05/25 17:17] – [Design notes:] oso
+++ graficar_variables_de_ups_en_grafana [2024/10/17 21:42] (current) – external edit 127.0.0.1
@@ Line 3: / Line 3: @@
 **Objective:** This project aims to monitor the status of a UPS (Uninterruptible Power Supply) by fetching data through a web service and storing it in a MariaDB database. The data is then visualized in a Grafana dashboard.
+{{:3029b882-0fff-4238-a832-c6ebfb5b5d8a.jpg?nolink|}}
+😅 //This image was created in May 2024, using Bing AI. Apparently it struggles a bit with the cable routing... ToDo: create this same prompt (raspberry pi connected to an UPS using an USB cable, photorealistic) in a couple of years.//
 ===== Code =====
@@ Line 23: / Line 26: @@
     host="localhost",
     port=3306,
-    user="facundo",
+    user="f******",
-    password="myPassword",
+    password="***my*assword***",
     database="UPSstats"
 )
@@ Line 96: / Line 99: @@
 </WRAP>
+===== Database Creation =====
+To create the database and the necessary tables for storing the UPS data in MariaDB, you would use the following SQL commands:
+. **Create the Database:**
+<code sql>
+CREATE DATABASE UPSstats;
+</code>
+. **Use the Database:**
+<code sql>
+USE UPSstats;
+</code>
+. **Create the `variables` Table:**
+This table will store the variable names and their corresponding IDs.
+<code sql>
+CREATE TABLE variables (
+    id_variable INT PRIMARY KEY,
+    variable_name VARCHAR(255) NOT NULL
+);
+</code>
+. **Insert the Variables:**
+Insert the variables you are interested in tracking into the `variables` table.
+<code sql>
+INSERT INTO variables (id_variable, variable_name) VALUES
+(1, 'battery.charge'),
+(2, 'battery.runtime'),
+(3, 'input.voltage'),
+(4, 'output.frequency'),
+(5, 'output.voltage'),
+(6, 'ups.load'),
+(7, 'ups.status');
+</code>
+. **Create the `ups_data` Table:**
+This table will store the actual data readings from the UPS.
+<code sql>
+CREATE TABLE ups_data (
+    id INT AUTO_INCREMENT PRIMARY KEY,
+    id_variable INT,
+    value VARCHAR(255),
+    timestamp DATETIME,
+    FOREIGN KEY (id_variable) REFERENCES variables(id_variable)
+);
+</code>
+With these commands, you will have a database named `UPSstats` with two tables: `variables` for storing the names and IDs of the variables, and `ups_data` for storing the actual readings along with their timestamps. Make sure to execute these commands in your MariaDB client to set up the database before running your Python script.
+<code sql>GRANT ALL PRIVILEGES ON UPSstats.* TO 'facundo'@'%' WITH GRANT OPTION;</code>
+<code sql>FLUSH PRIVILEGES;</code>
 ===== Flask Service =====
@@ Line 175: / Line 239: @@
 {{:upsstats.png?nolink|}}
+**Live site:**
+https://monitoring.facundoitest.space/public-dashboards/d26eff1b7dd548e6a5a88426efda10ba