MySQL Slow Query LOG GROK pattern for Graylog

Published

06 Nov 17:52

Last Push

06 Nov 17:52

Marketplace Rating

No rating yet

Discussion

7 Comments

Readme from Github

MySQL Slow Query Log GROK pattern for Graylog

Pattern

"name":"MYSQLSLOWQUERYLOG",
"pattern": "(?s) Time:%{SPACE}%{TIMESTAMP_ISO8601:mysql_slow_querydate}%{GREEDYDATA}User@Host: (?:%{USERNAME:mysql_slow_clientuser})\\[(?:%{DATA:mysql_slow_clientdbname})\\] @ (?:%{DATA:mysql_slow_clienthost}) \\[(?:%{DATA:mysql_slow_clientip})\\]%{SPACE}Id:%{SPACE}%{DATA:mysql_slow_id}%{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};(\\r|\\n)%{DATA:mysql_slow_query};"

Example message

# Time: 2020-11-06T23:04:23.313878Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     5
# Query_time: 7.000540  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1604703863;
select 1 as uno, 2 as due, 3 as tre, 4 as quattro, sleep(7);

Fields

mysql_slow_querydate: 2020-11-07 00:04:23 +01:00
mysql_slow_clienthost: localhost
mysql_slow_clientip: 127.0.0.1
mysql_slow_clientuser: root
mysql_slow_clientdbname: root
mysql_slow_locktime: 0
mysql_slow_id: 5
mysql_slow_querytime: 7.000540
mysql_slow_rowsexamined: 0
mysql_slow_rowssent: 1
mysql_slow_timestamp: 1604703863
mysql_slow_query: select 1 as uno, 2 as due, 3 as tre, 4 as quattro, sleep(7)

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.

Check

Created and tested on Graylog 3.3.8+e223f85 (Oracle Corporation 1.8.0_242 on Linux 3.10.0-1062.12.1.el7.x86_64)

Your Rating

Please sign in to rate this add-on.

Comments

zionio 2 months ago

Updated version for Graylog 3 pushed on GitHub

zionio 3 months ago

@dipinsugathan sorry for delay
Not yet tested on GL3, but you can easily extract pattern from content_pack.json and create your GROK pattern to apply to your pipeline/extractor.
Don't forget to create the right input (i'm using Filebeat as shipper) for the multiline message ingest.

dipinsugathan 4 months ago

We are getting the following error on version above 3.0. Any workaround ??

Unhandled exception in REST resource
java.lang.IllegalArgumentException: Unsupported content pack version: 0

dipinsugathan 4 months ago

Is this pack support version above Graylog 3.0 ?

pardeeprathi almost 3 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 over 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 almost 4 years ago

How to use?

Please sign in to comment.

Back to listing