Differences

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

--- graficar_variables_de_ups_en_grafana [2024/05/25 17:13] – [Code Explanation:] 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 146: / Line 210: @@
 In summary:
-. **Querying UPS status with 'upsc':**
-   - Linux uses the 'upsc' command to query the status of the UPS (Uninterruptible Power Supply) via the NUT (Network UPS Tools) driver. In this case, a driver, probably 'usbhid', is used to communicate with the UPS.
-   - 'upsc' provides various data about the UPS, such as battery charge level, remaining battery runtime, input and output voltage, among others.
-. **Executing 'upsc ups' command with Python:**
+  * Querying UPS status with 'upsc':
-   - In a Python script, the `subprocess` module is used to execute the 'upsc ups' command and capture the standard output (stdout). This allows integrating the UPS status query within the Python program's flow.
+    * Linux uses the 'upsc' command to query the status of the UPS (Uninterruptible Power Supply) via the NUT (Network UPS Tools) driver. In this case, a driver, probably 'usbhid', is used to communicate with the UPS.
+    * 'upsc' provides various data about the UPS, such as battery charge level, remaining battery runtime, input and output voltage, among others.
+  * Executing 'upsc ups' command with Python:
+    * In a Python script, the `subprocess` module is used to execute the 'upsc ups' command and capture the standard output (stdout). This allows integrating the UPS status query within the Python program's flow.
-. **Receiving HTTP requests with Flask:**
+  * Receiving HTTP requests with Flask:
-   - A Flask server runs on a Raspberry Pi, listening for HTTP requests on port 5000.
+    * A Flask server runs on a Raspberry Pi, listening for HTTP requests on port 5000.
-   - When a request is received at the URI '/run_command/upsc', Flask executes the 'upsc ups' command using `subprocess` and returns the result in ASCII format.
+    * When a request is received at the URI '/run_command/upsc', Flask executes the 'upsc ups' command using `subprocess` and returns the result in ASCII format.
-. **Querying UPS status from another server:**
+  * Querying UPS status from another server:
-   - From another server, a script performs the "heavier task":
+    * From another server, a script performs the "heavier task":
      - It connects to the MariaDB database.
      - Sends an HTTP request to the Raspberry Pi to execute 'upsc ups' and get the current UPS status.
      - Receives the response and uses regular expressions (regex) to parse the output and extract the relevant values.
-. **Storing the data in MariaDB:**
+  * Storing the data in MariaDB:
-   - The extracted data is inserted into the MariaDB database with a timestamp, assigning each value to its corresponding variable (e.g., 'battery.charge', 'input.voltage', etc.).
+    * The extracted data is inserted into the MariaDB database with a timestamp, assigning each value to its corresponding variable (e.g., 'battery.charge', 'input.voltage', etc.).
-   - This stored information is later used for visualizations and analysis in a Grafana dashboard.
+    * This stored information is later used for visualizations and analysis in a Grafana dashboard.
 ==== Results ====
@@ Line 174: / Line 239: @@
 {{:upsstats.png?nolink|}}
+**Live site:**
+https://monitoring.facundoitest.space/public-dashboards/d26eff1b7dd548e6a5a88426efda10ba