logstash gsub替换

最新推荐文章于 2024-07-03 21:27:40 发布

weixin_30675247

最新推荐文章于 2024-07-03 21:27:40 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/zhaoyangjian724/p/6198934.html

本文介绍了使用ELK栈进行数据库审计日志收集与分析的具体配置过程，包括Logstash的输入、过滤及输出配置，适用于MySQL数据库的日志监控场景。

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

{
        "message" => "192.168.11.186,192.168.11.187\t48391,3306\tDec  7, 2016 13:26:25.134545378\t\tSELECT \\x0a    r.trx_id waiting_trx_id,\\x0a    r.trx_mysql_thread_id waiting_thread,\\x0a    r.trx_query waiting_query,\\x0a    b.trx_id blocking_trx_id,\\x0a    b.trx_mysql_thread_id blocking_thread,\\x0a    b.trx_query blocking_query\\x0aFROM\\x0a    information_schema.innodb_lock_waits w\\x0a        INNER JOIN\\x0a    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id\\x0a        INNER JOIN\\x0a    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id",
       "@version" => "1",
     "@timestamp" => "2016-12-07T05:26:26.724Z",
           "path" => "/data01/audit/20161207_192.168.11.187.txt",
           "host" => "Vsftp",
           "type" => "audit-database-192.168.11.187",
       "clientip" => "192.168.11.186",
       "serverip" => "192.168.11.187",
    "client_port" => "48391",
    "server_port" => "3306",
           "time" => "Dec  7, 2016 13:26:25.134545378",
    "running_sql" => "SELECT      r.trx_id waiting_trx_id,     r.trx_mysql_thread_id waiting_thread,     r.trx_query waiting_query,     b.trx_id blocking_trx_id,     b.trx_mysql_thread_id blocking_thread,     b.trx_query blocking_query FROM     information_schema.innodb_lock_waits w         INNER JOIN     information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id         INNER JOIN     information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id"
}


{
        "message" => "192.168.11.186,192.168.11.187\t52481,3306\tDec  7, 2016 13:28:02.753832471\t\tSELECT      NOW(),  (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,     b.id,     b.user,     b.host,     b.db FROM     information_schema.innodb_trx a         INNER JOIN     information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id",
       "@version" => "1",
     "@timestamp" => "2016-12-07T05:28:03.459Z",
           "path" => "/data01/audit/20161207_192.168.11.187.txt",
           "host" => "Vsftp",
           "type" => "audit-database-192.168.11.187",
       "clientip" => "192.168.11.186",
       "serverip" => "192.168.11.187",
    "client_port" => "52481",
    "server_port" => "3306",
           "time" => "Dec  7, 2016 13:28:02.753832471",
    "running_sql" => "SELECT      NOW(),  (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,     b.id,     b.user,     b.host,     b.db FROM     information_schema.innodb_trx a         INNER JOIN     information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id"
}


[elk@Vsftp audit]$ cat logstash-audit.conf 
input {
        file {
                type => "audit-database-192.168.11.187"
                path => ["/data01/audit/*_192.168.11.187.txt"]
        }
    
}
filter {
    grok {
        match => [
             "message" ,"(?m)%{IPORHOST:clientip},%{IPORHOST:serverip}\s+(?<client_port>\S+),(?<server_port>\S+)\s+(?<time>(\S+\s+).*?[0-9]{2}:[0-9]{2}:[0-9]{2}\.\d+)\s+(?<running_sql>(\S+\s+).*)"
                ]
       }
   mutate {
        gsub =>["message","\\x0a"," "]
        gsub =>["running_sql","\\x0a"," "]
       }
}

      
output {
     if [type] == "audit-database-192.168.11.187" { 
        redis {
                host => "192.168.11.185"
                data_type => "list"
                key => "audit-database-192.168.11.187:redis"
                port=>"6379"
                password => "1234567"
        }
}
}

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6198934.html