logstash日志输出到mysql_Logstash将解析的日志插入MySQL数据库

最新推荐文章于 2024-05-17 20:32:56 发布

Esri北京研发中心

最新推荐文章于 2024-05-17 20:32:56 发布

阅读量595

点赞数 1

文章标签： logstash日志输出到mysql

本文链接：https://blog.csdn.net/weixin_28950015/article/details/113585362

版权

本文介绍了如何配置Logstash，通过安装logstash-output-jdbc插件，将解析后的nginx日志数据插入到MySQL数据库中。内容包括设置Dockerfile安装插件，解析日期、URL解码、获取IP信息、解析用户代理，以及转换字段类型。最后展示了输出到MySQL的jdbc配置，包括插入语句和所需字段。

摘要由CSDN通过智能技术生成

1.首先Logstash需要安装logstash-output-jdbc插件(以下方式基于docker)

Dockerfile如下:

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]

}