Logstash解析ログをMySQLデータベースに挿入
1.まずLogstashはlogstash-output-jdbcプラグイン(以下dockerベース)Dockerfileをインストールする必要があります.
2.nginxログの解析
FROM logstash:7.4.0
RUN sed -i 's#https://rubygems.org#https://gems.ruby-china.com#g' Gemfile && logstash-plugin install logstash-output-jdbc
2.nginxログの解析
date {
match => ["timestamp", "dd/MMM/yyyy:HH:mm:ss Z"] #
}
urldecode {
field => "Http_Request" #
}
geoip { # IP
database => "/usr/share/logstash/geodb/GeoLite2-City.mmdb"
source => "Client_IP"
target => "geoip"
fields => ["country_name","region_name", "city_name", "ip", "longitude", "latitude", "location"]
add_field => [ "[geoip][coordinates]", "%{[geoip][longitude]}" ]
add_field => [ "[geoip][coordinates]", "%{[geoip][latitude]}" ]
}
useragent { #
source => "User_Agent"
target => "ua"
}
mutate { #
convert => [ "[geoip][coordinates]", "float" ]
convert => [ "Http_Status_Code", "integer" ]
convert => [ "Http_Bytes", "integer" ]
convert => [ "Request_Time", "float" ]
convert => [ "Upstream_Response_Time", "float" ]
remove_field => [ "beat", "@version", "auth", "prospector", "source", "offset"]
gsub =>["timestamp","\ \+0800",""] # +0800
gsub =>["User_Agent","\"",""] #
}
output {
jdbc {
connection_string => "jdbc:mysql://mysql/elk?user=root&password=123456&useUnicode=true&characterEncoding=UTF8"
driver_jar_path => "/usr/share/logstash/geodb/mysql-connector-java-8.0.18.jar" # mysql
statement => ["INSERT INTO nginx_access_log(Client_IP,X_Forwarded_For,time,App_Version,Device,Device_Name,System_Version,Http_Method,Site,Http_Request,Http_Status_Code,Http_Referrer,Usercode,NetType,Openudid,Request_Time,Upstream_Response_Time,User_agent,city,province) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)","Client_IP","X_Forwarded_For","timestamp","App_Version","Device","Device_Name","System_Version","Http_Method","Site","Http_Request","Http_Status_Code","Http_Referrer","Usercode","NetType","Openudid","Request_Time","Upstream_Response_Time","User_Agent","[geoip][city_name]","[geoip][region_name]"]
# , [geoip][city_name]
}
}