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.

main.py

import mariadb
import requests
import datetime
import re
 
# Función para insertar un valor en la base de datos
def insert_value(cursor, id_variable, value):
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    query = "INSERT INTO ups_data (id_variable, value, timestamp) VALUES (?, ?, ?)"
    values = (id_variable, value, timestamp)
    cursor.execute(query, values)
    print(f"Valor con id_variable {id_variable} insertado en la base de datos: {value}")
 
# Conexión a la base de datos MySQL
db = mariadb.connect(
    host="localhost",
    port=3306,
    user="facundo",
    password="myPassword",
    database="UPSstats"
)
 
cursor = db.cursor()
 
# URL de la consulta HTTP
url = 'http://raspberry.facundoitest.space:5000/run_command/upsc'
 
# Hacer la solicitud HTTP
response = requests.get(url)
 
if response.status_code == 200:
    data = response.text
 
    # Definir las variables de interés y sus IDs
    variables = {
        'battery.charge': 1,
        'battery.runtime': 2,
        'input.voltage': 3,
        'output.frequency': 4,
        'output.voltage': 5,
        'ups.load': 6,
        'ups.status': 7
    }
 
    # Expresiones regulares para capturar los valores de interés
    regex_patterns = {
        'battery.charge': re.compile(r'battery\.charge:\s*(\d+)'),
        'battery.runtime': re.compile(r'battery\.runtime:\s*(\d+)'),
        'input.voltage': re.compile(r'input\.voltage:\s*(\d+\.?\d*)'),
        'output.frequency': re.compile(r'output\.frequency:\s*(\d+\.?\d*)'),
        'output.voltage': re.compile(r'output\.voltage:\s*(\d+\.?\d*)'),
        'ups.load': re.compile(r'ups\.load:\s*(\d+)'),
        'ups.status': re.compile(r'ups\.status:\s*(\w+)')
    }
 
    # Extraer y almacenar los valores de interés
    for var_name, var_id in variables.items():
        match = regex_patterns[var_name].search(data)
        if match:
            value = match.group(1)
            insert_value(cursor, var_id, value)
 
    # Confirmar los cambios
    db.commit()
else:
    print("Failed to fetch the UPS data")
 
# Cerrar la conexión
cursor.close()
db.close()

1. Database Insertion: The `insert_value` function inserts a value into the MariaDB database with the current timestamp. It uses a prepared statement to ensure the data is safely inserted.

2. Database Connection: The script connects to the MariaDB database hosted locally using specified credentials.

3. HTTP Request: The script sends a GET request to a URL hosted on a Raspberry Pi to fetch UPS data. The response is expected to be in plain text.

4. Regular Expressions: The script defines a set of regular expressions to extract specific variables from the UPS data returned by the HTTP request.

5. Data Extraction and Storage: The script iterates over the variables, applies the corresponding regex to extract the value, and inserts each value into the database.

6. Commit and Close: Changes are committed to the database, and the connection is closed.

flask_service.py

from flask import Flask
from subprocess import run, PIPE
 
app = Flask(__name__)
 
@app.route('/run_command/<command>')
def run_command(command):
    # Define the command to run based on the URL parameter
    if command == 'neofetch':
        cmd = ['neofetch']
    elif command == 'uptime':
        cmd = ['uptime']
    elif command == 'upsc':
        cmd = ['upsc', 'ups']
    else:
        return 'Invalid command'
 
    # Run the command and capture the output
    result = run(cmd, stdout=PIPE, stderr=PIPE, text=True)
 
    # Check if the command executed successfully
    if result.returncode == 0:
        # Wrap the output in <pre> tags to preserve formatting
        output = f'{result.stdout}'
        return output
    else:
        return f'Error running command: {result.stderr}'
 
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)

1. Flask Setup: A Flask web application is set up to handle HTTP requests on port 5000.

2. Command Execution: Based on the command specified in the URL, the Flask app runs a predefined shell command (`neofetch`, `uptime`, or `upsc`) using the `subprocess` module.

3. Output Handling: The output of the command is captured and returned as the HTTP response. If the command fails, an error message is returned.

The Grafana dashboard visualizes the UPS data in real-time, providing insights into various metrics like battery charge, input voltage, output frequency, and UPS status.