gork:https://gitbook.curiouser.top/origin/logstash-filter-grok.html

插件安装

.\bin\logstash-plugin install logstash-input-jdbc

编写配置文件

input {
    stdin {
    }
    jdbc {
    # mysql 数据库链接,test为数据库名
    jdbc_connection_string => "jdbc:mysql://home.cc:3306/faker"
    # 用户名和密码
    jdbc_user => "faker"
    jdbc_password => "admin666"
    # 驱动路径
    jdbc_driver_library => "F:\Elk\mysql-connector-java-8.0.17.jar"
    # 驱动类名
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "100"
    last_run_metadate_path => ""
     # 执行的sql 文件路径+名称
    statement_filepath => "F:\Elk\window\logstash-8.4.3\bin\mysql\jdbc.sql"
     # 设置监听间隔 各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
    schedule => "* * * * *"
    }
}

output {
#  elasticsearch {
#    hosts => ["http://es.cc"]
#    index => "faker"
#    document_id => "%{id}"
#    user => "elastic"
#    password => "elastic666"
#  }
  stdout {
      codec => json_lines
  }
}

编写sql语句

select * from faker where updated_at > :sql_last_value

启动

/bin/logstash -f mysql/jdbc.conf

收集nginx日志

 log_format tcp_format '$time_local|$remote_addr|$protocol|$status|$bytes_sent|$bytes_received|$session_time|$upstream_addr|$upstream_bytes_sent|$upstream_bytes_received|$upstream_connect_time';

nginx日志示例

192.168.2.50 - - [17/Jun/2023:18:02:50 +0800] "GET / HTTP/1.1" 403 548 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
192.168.2.50 - - [17/Jun/2023:18:02:52 +0800] "GET /favicon.ico HTTP/1.1" 404 548 "http://home.com:888/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
192.168.2.50 - - [19/Jun/2023:09:36:45 +0800] "GET /phpmyadmin_7238930f423aadc9 HTTP/1.1" 301 162 "http://home.com:10086/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
192.168.2.50 - - [19/Jun/2023:09:36:46 +0800] "GET /phpmyadmin_7238930f423aadc9/ HTTP/1.1" 200 3735 "http://home.com:10086/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
192.168.2.50 - - [19/Jun/2023:09:36:46 +0800] "GET /phpmyadmin_7238930f423aadc9/js/vendor/codemirror/addon/hint/show-hint.css?v=5.0.4 HTTP/1.1" 200 623 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"

logstash 规则

input {
  file {
      path => "/home/orangbus/Docker/elastic/weblogs/nginx/acess/gaobuba.com.log"
      type => "gaobuba_access_log" # 日志
      start_position => "beginning"
    }
    file {
          path => "/home/orangbus/Docker/elastic/weblogs/nginx/acess/gaozhidazhuan.com.log"
          type => "gaozhidazhuan_access_log" # 日志
          start_position => "beginning"
    }
}

filter {
    grok {
        match => {
        # $time_local|$remote_addr|$protocol|$status|$bytes_sent|$bytes_received|$session_time|$upstream_addr|$upstream_bytes_sent|$upstream_bytes_received|$upstream_connect_time
        # 192.168.2.50 - - [14/Jun/2023:14:43:28 +0800] "POST /api/login?phone=18388112576&password=admin666 HTTP/1.1" 500 6628 "-" "PostmanRuntime/7.31.1"
            "message" => '%{IPORHOST:client_ip} - - \[%{HTTPDATE:timestamp}\] \"%{WORD:http_method} %{DATA:request_path} HTTP/%{NUMBER:http_version}\" %{NUMBER:http_status} %{NUMBER:response_size} \"%{DATA:referrer}\" \"%{DATA:user_agent}\"'
        }
    }

}

output {
  stdout  {
    codec => rubydebug
  }
#  elasticsearch {
#    hosts => ["127.0.0.1:9200"]
#    index => "logstash_nginx_access_log"
#    user => "elastic"
#    password => "admin666"
#  }
}

规则设置

nginx

mysql

input {
  file {
      path => "/home/orangbus/Docker/elastic/weblogs/mysql/xjt-2020-slow.log"
      type => "xjt2020_mysql_slow_log" # 日志
      start_position => "beginning"
    }
}

filter {
    grok {
        match => {
            "message" =>[
                "^# Time: %{TIMESTAMP_ISO8601:log_timestamp}$",
                "^# Query_time: %{NUMBER:query_time}  Lock_time: %{NUMBER:lock_time} Rows_sent: %{NUMBER:rows_sent}  Rows_examined: %{NUMBER:rows_examined}$",
                "^SET timestamp＝%{NUMBER:timestamp};$",
                "^SELECT %{GREEDYDATA:query};$"
            ]
        }
    }
}

output {
#  stdout  {
#    codec => rubydebug
#  }
  elasticsearch {
    hosts => ["127.0.0.1:9200"]
    index => "xjt2020_mysql_slow"
    user => "elastic"
    password => "admin666"
  }
}