You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
95 lines
3.0 KiB
95 lines
3.0 KiB
#! /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()
|