Logstash is not processing MySQL logs correctly

elasticsearchgrokkibanalogstash

As you can see, the message is not just the message, but also contains the date and timestamp.

Valid XHTML.
Valid XHTML.

This is the MySQL Log: /var/log/mysql/error.log

150630  9:01:29 [Warning] Access denied for user 'test1'@'localhost' (using password: YES)
150630  9:03:39 [Warning] Access denied for user 'test3'@'localhost' (using password: YES)
150630  9:07:48 [Warning] Access denied for user 'test5'@'localhost' (using password: YES)
150630  9:10:00 [Warning] Access denied for user 'test7'@'localhost' (using password: YES)
150630  9:12:21 [Warning] Access denied for user 'test9'@'localhost' (using password: YES)

This is the Logstash-shipper configuration: /etc/logstash/shipper/conf.d/20-filter.conf

else if [type] == "mysql" {
  grok {
    patterns_dir => "/etc/logstash/patterns"
    match => [ "message", "%{MYSQLLOG}" ]
    overwrite => [ "message" ]
  }
}

This is the pattern:

MYSQLLOG %{NUMBER:date} %{TIME:time} \[%{LOGLEVEL:loglevel}\] %{GREEDYDATA:message}

I've been trying to fix this for weeks and I actually had it working at one moment, but without intervention from my end it stopped working again. Any tips regarding debugging?

Best Answer

It looks like there's an extra space in your log entries between the date and time fields, so your grok isn't matching, as evidenced by the existance of the _grokparsefailure tag.

Try this pattern instead: 

%{NUMBER:date}  %{TIME:time} \[%{LOGLEVEL:loglevel}] %{GREEDYDATA:message}

I've tested this in the Grok Constructor and it matched all the lines you provided.

Related Topic