import pymysql
import ipaddress
# 从rsyslog中提取IP地址。
def rsyslog_connect_db():
# 连接 rsyslog 数据库
return pymysql.connect(host='localhost',
port=3306,
user='rsyslog',
password='123456',
database='Syslog',
charset='utf8')
def ip_connect_db():
# 连接 zzszxyy 数据库
return pymysql.connect(host='localhost',
port=3306,
user='root',
password='',
database='zzszxyy',
charset='utf8')
def subnet_add(temp):
con1 = ip_connect_db()
cur1 = con1.cursor()
sql = "select count(*) from student_subnet where subnet = '%s' ;" % temp
# print(sql)
cur1.execute(sql)
rows1 = cur1.fetchall()
rows1 = rows1[0][0]
if rows1 == 0:
sql = "insert into student_subnet values('','%s','','');" % temp
print(sql)
cur1.execute(sql)
con1.commit()
cur1.close()
con1.close()
con = rsyslog_connect_db()
cur = con.cursor()
cur.execute('''select count(*) from SystemEvents where FromHost='s12708B' and message like '%->%' ;''')
rows = cur.fetchall()
totals = rows[0][0]
cur.execute(
''' select ID, message from SystemEvents where FromHost='s12708B' and message like '%->%' order by ID DESC;''')
ip_list = cur.fetchall()
ip_detail = ''
subnet = set()
ip_addrs = set()
for content in list(ip_list):
get_values = content[1].split(" ")
id = content[0]
interface = get_values[2]
src_ip = get_values[7].split("(")[0]
ip_addrs.add(src_ip)
src_net = str(ipaddress.ip_network(src_ip + '/24', strict=False))
dst_ip = get_values[9].split("(")[0]
ip_addrs.add(dst_ip)
dst_net = str(ipaddress.ip_network(dst_ip + '/24', strict=False))
# subnet.add(src_net)
# subnet.add(dst_net)
# ip_detail += "ID:{}, {}, {}, {}, {}, {} <br>\n ".format(id, interface, src_ip, src_net, dst_ip, dst_net)
print(len(ip_addrs))
con.commit()
cur.close()
con.close()
# 向数据库中写入子网数据
subnet = list(subnet)
subnet.sort()
for sub in list(subnet):
subnet_add(sub)