getsensordata

#!/usr/bin/env python3

############################################################
#                                                          #
# Get PRTG data from last month                            #
# and import into own backend database (e.g. MySQL)        #
#                                                          #
# Author: Jim Turnbull                                     #
# Modified: 04/01/2023                                     #
# Version 0.31                                             #
#                                                          #
############################################################

import sys, os

# Check for minimum Python version 3.9
v=[sys.version_info.major, sys.version_info.minor]

if v[0] < 3 or v[1] < 9:
    print("Python version 3.9 or above required to run this script")
    exit()

# Due to differences in Windows and Linux date formats, 
# get current locale date format and make it a 4-digit year
# which is common on Windows where PRTG will be installed
# Makes a reasonable assumption that the locale of the PRTG server
# is using the same locale as the server running this script
import locale
locale.setlocale(locale.LC_ALL, '')
dateformat=str(locale.nl_langinfo(locale.D_FMT)).replace('y','Y')

# Read in sections from config file
filename=str(os.path.dirname(os.path.abspath(__file__))) + "/config.ini"

try:
    from configparser import ConfigParser
except:
    print("Failed to load configparser, please make sure that the module is installed")
    exit()

try:
    parser=ConfigParser()
    parser.read(filename)
    config=parser["config"]
    isdaily=int(config.get("daily", fallback="0"))
    database=parser["database"]
    sensorarray=parser["sensors"]
    prtg=parser["prtg"]
    debug=parser["debug"]
    isdebug=int(debug.get("debug", fallback="0"))
    debugdays=int(debug.get("debugdays", fallback="4"))
    dbaction=str(database.get("action", fallback="fail"))
    dbport=int(database.get("port", fallback="0"))
    strdbport=":" + str(dbport) if dbport !=0 else ""
    db = str(database["database"])
except:
    print("Could not read configuration file, or required parameters are missing")
    exit()

# Clear line variable
clear='\x1b[2K'

# Set month from command line if passed, otherwise get from config file
if len(sys.argv) == 2:
    if int(sys.argv[1]) >= 0 and int(sys.argv[1]) <= 12:
        month=int(sys.argv[1])
    else:
        print("Please specify month between 1 and 12")
        exit()
else:
    month=int(parser["config"].get("month", fallback="0"))


# Construct PRTG variables
url=str("https://" + prtg["prtghost"] + "/api/historicdata.csv?")
apitoken=prtg["apitoken"]

# Construct database connection string for sqlalchemy
connectionstring=str(database["backend"] + "+" + 
    database["driver"] + "://" + 
    database["username"] + ":" + 
    database["password"] + "@" + 
    database["hostname"] + 
    strdbport + "/" + 
    database["database"])

# Import required Python modules
try:
    from datetime import datetime, timedelta
    from dateutil.relativedelta import relativedelta
    import pandas as pd
    from sqlalchemy import create_engine, false, inspect, schema, true
except:
    print("Could not load required modules. Try installing by running:\npip3 install -r requirements.txt")
    exit()

# Work out start and end dates, ensuring
# year goes to previous year if run in January
now=datetime.now()
if month==0:
    processmonth=(now - relativedelta(months=1))
    first_date=datetime(processmonth.year, processmonth.month, 1)
    end_date=datetime(now.year, now.month, 1)
# Work out start and end dates if month
# specified in config file
else:
    if month > 12:
        print("Month greater than 12, setting month to 12")
        month=12
    # If month in file is same as this month, assume that we want to download the data from same month last year (unless daily=1)
    if isdaily==1 and now.month==month:
        processmonth=now
        end_date=datetime(now.year, now.month, now.day)
    else:
        processmonth=(now - relativedelta(months=now.month + 12 - month if month > now.month else 12 if now.month==month else now.month - month))
        end_date=datetime(processmonth.year + 1 if month==12 else processmonth.year, 1 if month == 12 else month + 1, 1)
    first_date=datetime(processmonth.year, processmonth.month, 1)

year=processmonth.year

# For debug (less days) add debug="1" to the debug section in the ini file
if isdebug==1:
    end_date=end_date.replace(year=processmonth.year,month=processmonth.month,day=debugdays)

day_count=(end_date-first_date).days

lastpossday=(first_date+relativedelta(day=debugdays if isdebug else 31)).day

print("Fetching data from " + prtg["prtghost"] + " for " + first_date.strftime("%B %Y"))

try:
    engine = create_engine(connectionstring)
    insp=inspect(engine)
except:
    print("Could not access database", db)
    exit()


def exportdata(table,sensor):
    
    # Check if we already have data for the requested month for this sensor
    # and if so, then return immediately as we do not want to duplicate data
    hadtable=False
    if insp.has_table(table) and dbaction != "replace":
        hadtable=True
        havedays=0
        if isdaily==1:
            for single_date in ((first_date ) + timedelta(n) for n in range(day_count)):
                check=engine.execute("select * from " + str(table) + " where day(`DateTime`)=" + str(single_date.day) + 
                " and month(`DateTime`)=" + str(processmonth.month) + 
                " and year(`DateTime`)=" + str(year) + " limit 1;")
                if check.rowcount > 0:
                    print(clear + "Skipping " + str(table) + " - Data for " + single_date.strftime("%d %B %Y") + " already exists in database " + db +"\r", end='', flush=True )
                    havedays=single_date.day
            if havedays==lastpossday:
                return
            else:
                process_first_date=first_date.replace(day=havedays + 1)
                process_day_count=(end_date-process_first_date).days
        else:
            check=engine.execute("select * from " + str(table) + " where month(`DateTime`)=" + 
                str(processmonth.month) + " and year(`DateTime`)=" + str(year) + " limit 1;")
            if check.rowcount > 0:
                print(clear + "Skipping " + str(table) + " - Data for " + first_date.strftime("%B %Y") + " already exists in database " + db +"\r", end='', flush=True )
                return
            else:
                process_first_date=first_date
                process_day_count=(end_date-process_first_date).days

        if process_day_count < 1:
            print(clear + "Skipping " + str(table) + " - Data for " + first_date.strftime("%B %Y") + " already exists in database " + db +"\r", end='', flush=True )
            return

    # Build URL for the first day in the dataset
    # so that we have our column data and initial results

    print(clear + "Getting sensor data for " + str(table) + ": " + str(process_first_date),"\r", end='',flush=True)
    getURL=str(url +
        "id=" + sensor +
        "&sdate=" + process_first_date.strftime('%Y-%m-%d-00-00-00') +
        "&edate=" + (process_first_date + timedelta(days=1)).strftime('%Y-%m-%d-00-00-00') +
        "&avg=0&pctavg=0&pctshow=false&pct=0&pctmode=false" +
        "&apitoken=" + apitoken)

    # Get data from URL
    try:
        alldata=pd.read_csv(getURL)
    except:
        print(clear + "Could not fetch data from", url)
        exit()

    getURL=""

    # Now iterate through the remaining days and add them to the dataset
    for single_date in ((process_first_date + timedelta(days=1)) + timedelta(n) for n in range(process_day_count - 1)):
        print(clear + "Getting sensor data for " + str(table) + ": " + str(single_date),"\r", end='',flush=True)
        getURL=str(url +
        "id=" + sensor +
        "&sdate=" + single_date.strftime('%Y-%m-%d-00-00-00') +
        "&edate=" + (single_date + timedelta(days=1)).strftime('%Y-%m-%d-00-00-00') +
        "&avg=0&pctavg=0&pctshow=false&pct=0&pctmode=false" +
        "&apitoken=" + apitoken)
        alldata=pd.concat([alldata,pd.read_csv(getURL)],ignore_index=True, sort=False)

    print(clear + "Writing " + str(table) + " data to database " + db,"\r", end='',flush=True)

    # Strip spaces from column names
    alldata.columns = [c.replace(' ','') for c in alldata.columns]

    # Drop rows of data where PRTG has added
    # Sums or Averages which appear in the 'DateTime' field
    alldata = alldata[~alldata['DateTime'].str.contains('Sums')]
    alldata = alldata[~alldata['DateTime'].str.contains('Averages')]

    # Convert DateTime column to datetime
    alldata['DateTime']=pd.to_datetime(alldata['DateTime'], format=str(dateformat + " %X"))

    try:
        if hadtable==False:
            alldata.index += 1
            alldata.to_sql(table, con=engine, if_exists=dbaction, index=True, index_label='id')
            engine.execute('ALTER TABLE ' + str(table) + ' MODIFY id bigint AUTO_INCREMENT NOT NULL PRIMARY KEY;')
            engine.execute('CREATE INDEX ix_' + str(table) + '_datetime ON ' + str(table) + '  (DateTime);')
        else:
            alldata.to_sql(table, con=engine, if_exists=dbaction, index=False)
    except:
        print(clear + "Error writing to database", db)
        exit()

    print(clear + "Data for", table, "written to database", db, flush=True)


for sensor in sensorarray:
    exportdata(sensor,sensorarray[sensor])

print(clear + "Done.\n", end='',flush=True)