Python: 从rsyslog中提取IP地址信息

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)