Over-engineering the monitoring of my internet bandwidth usage

/**
Creates a DOM tree to locate current months data underneath the first graph on the page.

Xml.parse is deprecated so..... Not for the faint of heart.
*/
function xmlDerps(html) {
  //@TODO: This can probably be redone more intelligently
  var doc = Xml.parse(html, true); //Mmmmm deprecation
  var bodyHtml = doc.html.body.toXmlString();
  doc = XmlService.parse(bodyHtml);  
  var root = doc.getRootElement();
  
  //This is kind of kludgy; the html doesn't give us much to distinguish the <p> tags with though
  //hopefully the current month <p> tag remains the third one on the page
  var currentMonthRoot = getElementsByTagName(root, 'p')[2]
  
  //Get all child tags of our <p> that are <font> tags
  var fontChilds = getElementsByTagName(currentMonthRoot, 'font')
  
  var up = 0
  var down = 0
  var tot = 0
  
  //Should be 6 font tags total
  for each (f in fontChilds) {
    var fl = parseFloat(f.getText()) //Cast value to a float for comparisons
    var cl = f.getAttribute('style') //Load font attribute 'style' for checking text color
    
    if(fl < 2000 && cl.getValue().indexOf("color: red") > -1) {
      up = fl
    }
    if(fl < 2000 && cl.getValue().indexOf("color: green") > -1) {
      down = fl
    }
    if(fl < 2000 && cl.getValue().indexOf("color: blue") > -1) {
      tot = fl
    }
  }
  return [up, down, tot]
}

/**
Uses regex and String.search() to pull current month's data out of the history table at the bottom
of the page.
*/
function regexDerps(html) {
  var months = ["\\January", "\\February", "\\March", "\\April", "\\May", "\\June", "\\July", "\\August", "\\September", "\\October", "\\November", "\\December"];
  
  //Locate first instance of CURRENT_MONTH, CURRENT_YEAR ex: "October, 2015"
  var dateString = months[(new Date()).getMonth()]  
  dateString += ', ' + (new Date()).getFullYear()
  var i = html.search(dateString)
  
  //Locate first instance of LAST_MONTH, CURRENT_YEAR ex: "September, 2015"
  dateString = months[(new Date()).getMonth()-1]
  dateString += ', ' + (new Date()).getFullYear()
  var j = html.search(dateString)
  
  if(j == -1) {
    j = html.length //If for whatever reason last month's data isn't in the table...
  }
  
  html = html.substring(i, j) //Slice out just the section with the data we want  
  
  /**
  Lucky for us PTD color codes up, down, and total as red, green, and blue respectively.
  We can use that to isolate the data we want.
  */
  var red = html.substring(html.search('color: red') + 12)       // +12 because "color: red'>" is 12 characters long
  var green = html.substring(html.search('color: green') + 14)   // +14 because "color: green'>" is 14 characters long
  var blue = html.substring(html.search('color: blue')+ 13)      // +13 because "color: blue'>" is 13 characters long
  
  //Our values are at the begining of these string now with a bunch of garbage after them
  //Substring from 0 to the next occurence of "<" to trim out what we want
  red = red.substring(0, red.search('<'))
  green = green.substring(0, green.search('<'))
  blue = blue.substring(0, blue.search('<'))
  
  return [parseFloat(red), parseFloat(green), parseFloat(blue)]
}

/**
Used a proper regex to parse the html in ~10 lines.

This should be faster and more reliable than previous parsing methods as well as more resistant to page restructuring; as
long as the <h4> tag and the 3 <font> tags remain on the page in the same order of appearance this will not break.
*/
function regexDerpsv2(html) {
  html = html.replace(/\r?\n|\r/g,""); //remove newlines because I cbf to rewrite the pattern below
  var re = /<h4>This Month.+?<font style='color: red'>([-+]?[0-9]*\.?[0-9]+).+?<font style='color: green'>([-+]?[0-9]*\.?[0-9]+).+?<font style='color: blue'>([-+]?[0-9]*\.?[0-9]+)/;
  var m; 
  if ((m = re.exec(html)) !== null) { //Did we find a match?
      if (m.index === re.lastIndex) { //I don't know what this does lul...
          re.lastIndex++;
      }
      //upstream, downstream, and total values are defined in the regex as the 1st, 2nd, and 3rd capture groups
      return [parseFloat(m[1]), parseFloat(m[2]), parseFloat(m[3])]
  }
  return [-1, -1, -1]
}

/**
Request bandwidth usage page from PTD
Parse upstream, downstream, and total values from html
Add row to spreadsheet
Send quota alerts if needed
*/
function update(upload, download) {
  //var url = 'https://cable_company_bandwidth_graph_utility'
  
  //MAKE SURE TO COMMENT LINE 12 AND UN-COMMENT LINE 13 WHEN TESTING!!
  //var html = UrlFetchApp.fetch(url).getContentText()
  //var html = getTestHtml()  
  
  //var parse = xmlDerps(html)    //This method no longer works, the deprecated methods it uses have finally been removed afaik
  //var parse = regexDerps(html)  //Garbage :)
  //var parse = regexDerpsv2(html)  //Fastest/Best. Regex is ugly, but when isn't regex ugly? :P
  
  var up = upload
  var down = download
  var tot = upload + download

#!/usr/bin/env python3

from datetime import datetime
import json, re, requests

WEBHOOK_URL = "https://script.google.com/macros/s/WEBHOOK_ID/exec"
PTD_BANDWIDTH_GRAPH_URL = "https://cable_company_bandwidth_graph_utility"
PATTERN = r"<h4>This Month[\s\S]+?Total Upstream:[\s\S]+?GB \(<font style='color: red'>(?P<upload>\d+)[\s\S]+?Total Downstream: [\s\S]+?GB \(<font style='color: green'>(?P<download>\d+)"

def fetch_webpage(url):
    try:
        response = requests.get(url)
        
        if response.status_code == 200:
            return response.text
        else:
            return f"Error: {response.status_code} - Unable to fetch webpage"
    except Exception as e:
        return f"Error: {str(e)} - Unable to fetch webpage"

html = fetch_webpage(PTD_BANDWIDTH_GRAPH_URL) # fetch page
matches = re.finditer(PATTERN, html) # run regex

data = []
for match in matches:
    data = [int(match.group('upload')), int(match.group('download'))] # regex grabs number of bytes

json_payload = json.dumps([data[0]/1024/1024/1024, data[1]/1024/1024/1024]) # bytes -> GB, divide values by 1,073,741,824
print('payload:', json_payload)
response = requests.post(WEBHOOK_URL, json=json_payload) # send JSON payload to Google Sheet

if response.status_code == 200:
    print("POST request successful.", response.text)
else:
    print("POST request failed:", response.status_code)

@app.route('/bandwidth-GB', methods=['GET'])
def get_bandwidth_usage_GB():
    attributes = {}
    row = magical_function_that_gets_the_data_we_want_to_serve()
    attributes["timestamp"] = row[0]
    attributes["upstream"] = int(row[1])/1024/1024/1024
    attributes["downstream"] = int(row[2])/1024/1024/1024
    attributes["total"] = int(row[3])/1024/1024/1024
    return jsonify(attributes)

@app.route('/')
def hello_world():
    return '<h1>API is alive</h2>'

if __name__ == '__main__':
   app.run(host="0.0.0.0", port=5000)

rest:
  - scan_interval: 600
    resource: http://nalthis.c0smere.net:42070/bandwidth-GB
    sensor:
      - name: Internet Bandwidth Used
        value_template: "{{ value_json.total | round(3) }}"
        unit_of_measurement: GB
      - name: Internet Upstream Bandwidth Used
        value_template: "{{ value_json.upstream | round(3) }}"
        unit_of_measurement: GB
      - name: Internet Downstream Bandwidth Used
        value_template: "{{ value_json.downstream | round(3) }}"
        unit_of_measurement: GB

docker run --name pgsql -e POSTGRES_PASSWORD=pa$$w0rd -d -v ${PWD}/db:/var/lib/postgresql/data -p 5432:5432 postgres

CREATE TABLE IF NOT EXISTS "SECV_SCRAPE".scrape_data
(
    time_stamp timestamp without time zone NOT NULL,
    upstream bigint NOT NULL,
    downstream bigint NOT NULL,
    total bigint NOT NULL,
    CONSTRAINT scrape_data_pkey PRIMARY KEY (time_stamp)
)

#!/usr/bin/env python3

from datetime import datetime
import json, re, requests, psycopg2

WEBHOOK_URL = "https://script.google.com/macros/s/webhook_id/exec"
PTD_BANDWIDTH_GRAPH_URL = "https://cable_company_graph_util"
PATTERN = r"<h4>This Month[\s\S]+?Total Upstream:[\s\S]+?GB \(<font style='color: red'>(?P<upload>\d+)[\s\S]+?Total Downstream: [\s\S]+?GB \(<font style='color: green'>(?P<download>\d+)"

db_params = {
    'dbname': 'gp0',
    'user': 'user',
    'password': 'pa$$w0rd',
    'host': '10.0.0.1',
    'port': '5432'
}

insert_query = """
    INSERT INTO "SECV_SCRAPE".scrape_data (time_stamp, upstream, downstream, total)
    VALUES (%s, %s, %s, %s)
"""

def log_to_database(up, down):
    tot = up + down
    data = [datetime.now(), up, down, tot]

    # Connect to the database using a context manager
    with psycopg2.connect(**db_params) as conn:
        with conn.cursor() as cursor:
            cursor.execute(insert_query, data)
        conn.commit()

def fetch_webpage(url):
    try:
        response = requests.get(url)
        
        if response.status_code == 200:
            return response.text
        else:
            return f"Error: {response.status_code} - Unable to fetch webpage"
    except Exception as e:
        return f"Error: {str(e)} - Unable to fetch webpage"

html = fetch_webpage(PTD_BANDWIDTH_GRAPH_URL) # fetch page
matches = re.finditer(PATTERN, html) # run regex

data = []
for match in matches:
    data = [int(match.group('upload')), int(match.group('download'))] # regex grabs number of bytes

log_to_database(data[0], data[1])

json_payload = json.dumps([data[0]/1024/1024/1024, data[1]/1024/1024/1024]) # convert to GB to send up to Google sheet
print('payload:', json_payload)
response = requests.post(WEBHOOK_URL, json=json_payload) # send JSON payload to Google Sheet

if response.status_code == 200:
    print("POST request successful.", response.text)
else:
    print("POST request failed:", response.status_code)

import psycopg2
from flask import Flask, jsonify
from datetime import datetime

db_params = {
    'dbname': 'gp0',
    'user': 'user',
    'password': 'pa$$w0rd',
    'host': '10.0.0.1',
    'port': '5432'
}

select_query = """
    SELECT * FROM "SECV_SCRAPE".scrape_data
    ORDER BY time_stamp DESC
    LIMIT 1
"""

@app.route('/bandwidth-GB', methods=['GET'])
def get_bandwidth_usage_GB():
    attributes = {}
    with psycopg2.connect(**db_params) as conn:
        with conn.cursor() as cursor:
            cursor.execute(select_query)
            rows = cursor.fetchall()
            for row in rows:
                attributes["timestamp"] = row[0]
                attributes["upstream"] = int(row[1])/1024/1024/1024
                attributes["downstream"] = int(row[2])/1024/1024/1024
                attributes["total"] = int(row[3])/1024/1024/1024
    return jsonify(attributes)

@app.route('/')
def hello_world():
    return '<h1>API is alive</h2>'

if __name__ == '__main__':
   app.run(host="0.0.0.0", port=5000)

sudo docker rm $(docker stop $(docker ps -a -q --filter="name=magical_blackburn" --format="{{.ID}}"))
sudo docker build -t secv-api .
sudo docker run --name magical_blackburn -dp 42070:5000 secv-api

- SECV Internet Statistics:
    - Current Month Bandwidth:
        icon: mikrotik
        server: my-docker
        container: magical_blackburn
        widget:
          type: customapi
          url: http://192.168.88.5:42070/bandwidth-GB
          refreshInterval: 600000
          method: GET
          mappings:
            - field: upstream
              label: Up
              format: float
              suffix: GB
            - field: downstream
              label: Down
              format: float
              suffix: GB
            - field: total
              label: Total
              format: float
              suffix: GB

WITH a
     AS (SELECT Row_number()
                  over (
                    ORDER BY scrape_data.time_stamp DESC) AS row_number,
                scrape_data.time_stamp,
                scrape_data.upstream,
                scrape_data.downstream,
                scrape_data.total
         FROM   "SECV_SCRAPE".scrape_data),
     b
     AS (SELECT Row_number()
                  over (
                    ORDER BY scrape_data.time_stamp DESC) AS row_number,
                scrape_data.time_stamp,
                scrape_data.upstream,
                scrape_data.downstream,
                scrape_data.total
         FROM   "SECV_SCRAPE".scrape_data)
SELECT b.time_stamp                                                         AS
       period_start,
       a.time_stamp                                                         AS
       period_end,
       a.time_stamp - b.time_stamp                                          AS
       duration,
       a.upstream - CASE
                      WHEN To_char(b.time_stamp, 'MM' :: text) <>
                           To_char(a.time_stamp, 'MM' :: text) THEN 0 :: bigint
                      ELSE b.upstream
                    END                                                     AS
       u_diff,
       a.downstream - CASE
                        WHEN To_char(b.time_stamp, 'MM' :: text) <>
                             To_char(a.time_stamp, 'MM' :: text) THEN 0 ::
                        bigint
                        ELSE b.downstream
                      END                                                   AS
       d_diff,
       a.total - CASE
                   WHEN To_char(b.time_stamp, 'MM' :: text) <>
                        To_char(a.time_stamp, 'MM' :: text) THEN 0 :: bigint
                   ELSE b.total
                 END                                                        AS
       t_diff,
       ( a.upstream - CASE
                        WHEN To_char(b.time_stamp, 'MM' :: text) <>
                             To_char(a.time_stamp, 'MM' :: text) THEN 0 ::
                        bigint
                        ELSE b.upstream
                      END ) :: NUMERIC / Extract(epoch FROM a.time_stamp -
                                         b.time_stamp)                      AS
       u_rate,
       ( a.downstream - CASE
                          WHEN To_char(b.time_stamp, 'MM' :: text) <>
                               To_char(a.time_stamp, 'MM' :: text) THEN 0 ::
                          bigint
                          ELSE b.downstream
                        END ) :: NUMERIC / Extract(epoch FROM a.time_stamp -
                                                              b.time_stamp) AS
       d_rate,
       ( a.total - CASE
                     WHEN To_char(b.time_stamp, 'MM' :: text) <>
                          To_char(a.time_stamp, 'MM' :: text) THEN 0 :: bigint
                     ELSE b.total
                   END ) :: NUMERIC / Extract(epoch FROM a.time_stamp -
                                                         b.time_stamp)
                                                             AS t_rate
FROM   a
       join b
         ON ( a.row_number + 1 ) = b.row_number; 

BEGIN
WITH cte AS (
    SELECT *
          ,extract('epoch' FROM SUM(duration) OVER (ORDER BY period_start DESC)) AS x
    FROM "SECV_SCRAPE".scrape_data_intervals
    )
INSERT INTO "SECV_SCRAPE".rolling_24hr_summary
SELECT MIN(period_start) AS period_start
      ,MAX(period_end) AS period_end
      ,SUM(duration) AS duration
      ,SUM(u_diff) AS u_diff
      ,SUM(d_diff) AS d_diff
      ,SUM(t_diff) AS t_diff
      ,SUM(u_diff)/extract('epoch' FROM SUM(duration)) AS u_rate
      ,SUM(d_diff)/extract('epoch' FROM SUM(duration)) AS d_rate
      ,SUM(t_diff)/extract('epoch' FROM SUM(duration)) AS t_rate
	  ,(((((MAX(cap.cap_bytes)/extract(days FROM date_trunc('month', MIN(period_start)) + interval '1 month - 1 day'))/24)/60)/60) * extract('epoch' FROM SUM(duration))) AS cap_bytes
	  ,SUM(t_diff) / (((((MAX(cap.cap_bytes)/extract(days FROM date_trunc('month', MIN(period_start)) + interval '1 month - 1 day'))/24)/60)/60) * extract('epoch' FROM SUM(duration))) AS pct_cap_util
FROM cte
LEFT JOIN "SECV_SCRAPE".bandwidth_cap_history cap
	ON period_start BETWEEN cap.start_date and cap.end_date
WHERE x < 87300; -- 24.25 hours in seconds, to allow for some overshoot because of varying execution times
RETURN NEW;
END

SELECT period_start
      ,period_end
      ,u_diff/1024.0/1024/1024 AS u_diff_GB
      ,d_diff/1024.0/1024/1024 AS d_diff_GB
      ,t_diff/1024.0/1024/1024 AS t_diff_GB
      ,u_rate/1024.0/1024 AS u_rate_MBs
      ,d_rate/1024.0/1024 AS d_rate_MBs
      ,t_rate/1024.0/1024 AS t_rate_MBs
      ,cap_bytes/1024.0/1024/1024 AS cap_GB
      ,pct_cap_util*100 AS pct_cap_util
FROM "SECV_SCRAPE".rolling_24hr_summary
ORDER BY period_end DESC
LIMIT 1

def generic_query(q):
    attributes = {}
    with psycopg2.connect(**db_params) as conn:
        with conn.cursor() as cursor:
            cursor.execute(q)
            rows = cursor.fetchall()
            col_names = [desc[0] for desc in cursor.description]
            
            for row in rows:
                for col_name, value in zip(col_names, row):
                    attributes[col_name] = value
                    
    return jsonify(attributes)

@app.route('/rolling24', methods=['GET'])
def get_rolling_24_data():
    return generic_query(rolling_query)

    - Rolling 24hr Statistics:
        icon: mikrotik
        server: my-docker
        container: magical_blackburn
        widget:
          type: customapi
          url: http://192.168.88.5:42070/rolling24
          refreshInterval: 600000
          method: GET
          display: list
          mappings:
            - field: period_start
              label: Report Period
              format: date
              locale: en-GB
              timeStyle: short
              additionalField:
                field: period_end
                label: end
                format: date
                locale: en-GB
                timeStyle: short
            - field: u_rate_mbs
              label: Upload/Download Average Rate
              format: float
              suffix: MB/s
              additionalField:
                field: d_rate_mbs
                label: Download
                format: float
                suffix: MB/s
            - field: t_rate_mbs
              label: Combined
              format: float
              suffix: MB/s
            - field: u_diff_gb
              label: Upstream/Downstream Bandwidth
              format: float
              suffix: GB
              additionalField:
                field: d_diff_gb
                label: Downstream
                format: float
                suffix: GB
            - field: t_diff_gb
              label: Combined
              format: float
              suffix: GB
            - field: pct_cap_util
              label: Capacity 
              format: percent