kea_for_miners/kea_host_wacher.py

#! /usr/bin/python3
# coding=utf-8

import MySQLdb
import datetime
from kea_hosts_generane import read_table, read_conf

qinsert_last_seen = "insert into was_last_seen (id_1c_import, datetime) " \
          "values (%s, now());"

qupdate_last_seen = "update was_last_seen set datetime = now() where id_1c_import = '%s';"

qdelete_last_seen = "delete from was_last_seen where id_1c_import = '%s';"

qdelete_host = "DELETE " \
               "FROM" \
               "    hosts " \
               "WHERE" \
               "   dhcp_identifier " \
               "IN " \
               "  (SELECT " \
               "    unhex(replace(hw_addr, ':', '')) AS hw_addr" \
               "   FROM 1c_import" \
               "   WHERE  id like '%s');"

qonline = "select " \
          "    1c_import.id as 1c_id " \
          "from 1c_import" \
          " right join lease4 " \
          "    on (unhex(replace(1c_import.hw_addr, ':', '')) = lease4.hwaddr)" \
          "WHERE " \
          "INET_NTOA(address) not like '10.%.0.%';"

qselect_last_seen = "select * from was_last_seen;"


def update_seen(sql, id, dbhost, dbuser, dbpass, dbbase):
    try:
        conn = MySQLdb.connect(host=dbhost, user=dbuser,
                               passwd=dbpass, db=dbbase)
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql % id)
        data = cur.fetchall()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
    conn.commit()
    conn.close()
    return data


def update_hosts(sql, id, dbhost, dbuser, dbpass, dbbase):
    try:
        conn = MySQLdb.connect(host=dbhost, user=dbuser,
                               passwd=dbpass, db=dbbase)
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        print(sql % id)
        cur.execute(sql % id)
        data = cur.fetchall()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
    conn.commit()
    conn.close()
    return data


def main():
    conf = read_conf()
    dbconf = [conf['dbhost'], conf['dbuser'], conf['dbpass'], conf['dbbase']]
    online_hosts = read_table(qonline, *dbconf)
    seen = read_table(qselect_last_seen, *dbconf)
    seen_list = []
    for seen_h in seen:
        seen_list.append(seen_h['id_1c_import'])
    for online_host in online_hosts:
        if online_host['1c_id'] in seen_list:
            update_seen(qupdate_last_seen, online_host['1c_id'], *dbconf)
        elif online_host not in seen:
            update_seen(qinsert_last_seen, online_host['1c_id'], *dbconf)
    seen = read_table(qselect_last_seen, *dbconf)
    for seen_host in seen:
        if seen_host['datetime'] < (datetime.datetime.now() - datetime.timedelta(minutes=1)):
            update_hosts(qdelete_host, seen_host['id_1c_import'], *dbconf)
            update_seen(qdelete_last_seen, seen_host['id_1c_import'], *dbconf)


if __name__ == '__main__':
    main()