用於 mysql 查詢的 logstash 多行日誌

我希望將 mysql-proxy lua 腳本中的日誌推送到 lostash 中。一個範例日誌可能是

[2015-03-09 11:13:47] USER:username IP:10.102.51.134:41420 DB:dbName Query: -- One Pager Trends 
-- params:

SELECT 
 date,
 SUM(t.rev) revenue,
 SUM(t.rev - t.cost) profit 
FROM
 am.s_d t
 INNER JOIN am.event e 
   ON t.`event_id` = e.`event_id`
WHERE 1=1 AND DATE BETWEEN '2014-12-08' AND '2015-03-08'
 AND t.source_id = 25
GROUP BY date
[2015-03-09 11:17:28] USER:mzupan IP:10.102.22.216:49843 DB: Query: show databases

新的日誌條目將始終以[

所以我正在使用 logstash-forwarder 運送日誌並進行如下處理

filter {

 if [type] == "mysql-proxy" {
   grok {
     match => { "message" => "\[%{TIMESTAMP_ISO8601}\] USER:%{WORD:user} IP:%{IP:ip}:%{INT} DB:%{DATA:db} Query: (?<query>(.|\r|\n)*)" }
   }
   multiline {
     pattern => "^\["
     what => "previous"
     negate=> true
   }
   date {
     match => [ "timestamp", "yyyy-MM-dd HH:mm:ss" ]
   }
 }
}

我的問題是在 kibana 我看到類似以下 json 的查詢

{
 "_index": "logstash-2015.03.09",
 "_type": "mysql-proxy",
 "_id": "AUv_vj3u0BuDzneUoKKc",
 "_score": null,
 "_source": {
   "message": "[2015-03-09 11:13:47] USER:username IP:10.102.51.134:41420 DB:dbName Query: -- One Pager Trends \n-- params:\n\nSELECT \n  date,\n  SUM(t.rev) revenue,\n  SUM(t.rev - t.cost) profit \nFROM\n  am.s_d t\n  INNER JOIN am.event e \n    ON t.`event_id` = e.`event_id`\nWHERE 1=1 AND DATE BETWEEN '2014-12-08' AND '2015-03-08'\n  AND t.source_id = 25\nGROUP BY date",
   "@version": "1",
   "@timestamp": "2015-03-09T18:13:52.287Z",
   "type": "mysql-proxy",
   "file": "/var/log/mysql-queries.log",
   "host": "an01.domain.com",
   "offset": [
     "11855847",
     "11855943",
     "11855954",
     "11855955",
     "11855963",
     "11855971",
     "11855993",
     "11856023",
     "11856028",
     "11856039",
     "11856064",
     "11856099",
     "11856156",
     "11856179",
     "11856193",
     "11856194"
   ],
   "user": "username",
   "ip": "10.102.51.134",
   "db": "dbname",
   "query": "-- One Pager Trends ",
   "tags": [
     "_grokparsefailure",
     "multiline"
   ]
 },
 "fields": {
   "@timestamp": [
     1425924832287
   ]
 },
 "sort": [
   1425924832287
 ]
}

即使logstash 似乎正確設置了消息，我也只看到了第一部分。

過濾器中的多行應放在匹配部分之前。嘗試像這樣配置它：

篩選 {
如果 [類型] == "mysql-proxy" {
多行{
模式 => "^\["
什麼=>“以前”
否定 => 真
}
摸索{
match => { "message" => "\[%{TIMESTAMP_ISO8601}\] USER:%{WORD:user} IP:%{IP:ip}:%{INT} DB:%{DATA:db} 查詢: ( ?(.|\r|\n)*)" }
}
日期 {
匹配 => [“時間戳”，“yyyy-MM-dd HH:mm:ss”]
}
}

這適用於我的 logstash v1.4.2。

引用自：https://serverfault.com/questions/674109