MySQL Slow Query LOG GROK pattern for Graylog

Published

05 Oct 07:52

Last Push

16 Apr 09:44

Marketplace Rating

No rating yet

Discussion

3 Comments

Readme from Github

MySQL Slow Query Log GROK pattern for Graylog

Pattern

"name":"MYSQLSLOWQUERYLOG",
"pattern":"(?s) User@Host: (?:%{USERNAME:mysql_slow_clientuser})(?:%{GREEDYDATA}) @ (?:%{DATA:mysql_slow_clienthost}) \\[(?:%{DATA:mysql_slow_clientip}\\])%{SPACE}Id:%{SPACE}%{DATA:mysql_slow_id}(\\r|\\n)%{GREEDYDATA}Query_time: %{NUMBER:mysql_slow_querytime:float}(?:%{SPACE})Lock_time: %{NUMBER:mysql_slow_locktime:float}(?:%{SPACE})Rows_sent: %{NUMBER:mysql_slow_rowssent:int}(?:%{SPACE})Rows_examined: %{NUMBER:mysql_slow_rowsexamined:int}(?:%{SPACE})(?:%{GREEDYDATA})SET timestamp=%{NUMBER:mysql_slow_timestamp}\\;"

Example message

# Time: 2016-10-05T12:14:29.634170+01:00
# User@Host: user1[user1] @ localhost [127.0.0.1]  Id: 222521
# Query_time: 12.000243  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1475662469;
select sleep(12);

Fields

mysql_slow_clienthost: localhost
mysql_slow_clientip: 127.0.0.1
mysql_slow_clientuser: user1
mysql_slow_locktime: 0.000000
mysql_slow_id: 222521
mysql_slow_querytime: 12.000243
mysql_slow_rowsexamined: 0
mysql_slow_rowssent: 1
mysql_slow_timestamp: 1475662469

Input

A way to grab log lines: Graylog Collector Sidecar with Beats (Filebeat)

Installation

Go to Graylog Web Interface -> System -> Content Packs then select content_pack.json file and upload it.

Your Rating

Please sign in to rate this add-on.

Comments

pardeeprathi over 2 years ago

Hi,

I'm trying this pattern with below message. But it's not working. Can you please help me out.

# User@Host: test[test] @ [1.2.3.4]
# Thread_id: 175092484 Schema: testdb QC_hit: No
# Query_time: 1.596409 Lock_time: 0.000142 Rows_sent: 37208 Rows_examined: 460730
SET timestamp=1520476663;
SELECT
created_on,
tenant_id,
count(DISTINCT pricing_id) as total_book_click

FROM testdb.pr_xml
where created_on >= '2018-02-15 00:00:00' and xml_type='REQUEST' and tenant_id in(32,34,80,81,82,83)
group by created_on,tenant_id;
# Time: 180308 8:07:48

razvanvlasin almost 3 years ago

Download content_pack.json here: https://github.com/zionio/graylog_grok_mysqlslowquery

Then install it using Graylog Web Interface -> System -> Content Packs

rafaelcarsetimo over 3 years ago

How to use?

Please sign in to comment.

Back to listing