modsecurity日志解析并存到mysql数据库

最新推荐文章于 2024-07-22 07:00:00 发布

原创最新推荐文章于 2024-07-22 07:00:00 发布 · 2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#python #modsecurity

python 同时被 2 个专栏收录

1 篇文章

订阅专栏

modsecurity

1 篇文章

订阅专栏

本文介绍了一个Python脚本，用于解析ModSecurity日志并将其存储到MySQL数据库中，同时自动清理旧日志，有效提升日志管理和安全监控效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Github:https://github.com/bestmarkma/modsecurity_log_mysql

设置modsecurity_log_mysql.py定时任务，实现modsecurity日志解析并存到mysql数据库，并自动清除旧日志。

python代码(modsecurity_log_mysql.py)：

#!/usr/bin/python3
# -*- coding: UTF-8 -*-

import re
import pymysql
import datetime


# 打开数据库连接
db = pymysql.Connect(
  host='127.0.0.1',
  port=3306,
  user='waf',
  passwd='waf',
  db='waf',
  charset='utf8'
)

# 使用cursor()方法获取操作游标
cursor = db.cursor()

#打开日志
f = open('/var/log/modsec_audit.log')
log = f.read()

it = re.finditer(r"---[\da-zA-Z]{8}---A--[\s\S]*?---[\da-zA-Z]{8}---Z--",log)
for match in it:
    #m_original
    m_original = match.group()

    #日期时间
    m_datetime = re.search(r"\d\d/[a-zA-Z]{3}/\d{4}:\d{2}:\d{2}:\d{2}", match.group()).group()
    m_datetime = datetime.datetime.strptime(m_datetime, "%d/%b/%Y:%H:%M:%S").strftime("%Y-%m-%d %H:%M:%S")

    # host
    m_host = re.search(r"Host:.*", match.group()).group().replace('Host: ','')

    # id
    m_id = []
    it_kid = re.finditer('\[id\s"\d+?"\]', match.group())
    for match_kid in it_kid:
        m_id.append(match_kid.group().replace('[id "','').replace('"]',''))

    # hostname
    m_hostname = []
    it_kid = re.finditer('\[hostname\s".*?"\]', match.group())
    for match_kid in it_kid:
        m_hostname.append(match_kid.group().replace('[hostname "','').replace('"]',''))

    # uri
    m_uri = []
    it_kid = re.finditer('\[uri\s".*?"\]', match.group())
    for match_kid in it_kid:
        m_uri.append(match_kid.group().replace('[uri "','').replace('"]',''))
        
    # data
    m_data = []
    it_kid = re.finditer('\[data\s".*?"\]', match.group())
    for match_kid in it_kid:
        m_data.append(match_kid.group().replace('[data "','').replace('"]',''))

    # msg
    m_msg = []
    it_kid = re.finditer('\[msg\s".*?"\]', match.group())
    for match_kid in it_kid:
        m_msg.append(match_kid.group().replace('[msg "','').replace('"]',''))

    for index in range(len(m_id)):

        # SQL 插入语句
        sql = "INSERT INTO `log` (`id`, `m_datetime`, `m_host`, `m_id`, `m_hostname`, `m_uri`, `m_data`, `m_msg`, `m_original`) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s)"

        #print("sql:" + sql)

        try:
            # 执行sql语句
            cursor.execute(sql,(m_datetime, m_host, m_id[index], m_hostname[index], m_uri[index],m_data[index], m_msg[index], m_original))
            # 提交到数据库执行
            db.commit()
        except:
            # 发生错误时回滚
            print("发生错误sql:" + sql % (m_datetime, m_host, m_id[index], m_hostname[index], m_uri[index],m_data[index], m_msg[index], m_original))
            db.rollback()

# 关闭数据库连接
db.close()

# 清空日志
f.close()
f = open('/var/log/modsec_audit.log','w')
f.write('')
f.close()

数据库sql:

CREATE TABLE `log` (
  `id` int(11) UNSIGNED NOT NULL,
  `m_datetime` datetime DEFAULT NULL,
  `m_host` varchar(100) DEFAULT NULL,
  `m_id` varchar(20) DEFAULT NULL,
  `m_hostname` varchar(100) DEFAULT NULL,
  `m_uri` varchar(200) DEFAULT NULL,
  `m_data` varchar(2000) NOT NULL,
  `m_msg` varchar(1000) DEFAULT NULL,
  `m_original` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;