topic Re: How to extract query from MySQL Slow Query logs? in Splunk Search

How to extract query from MySQL Slow Query logs?

brandonpal — Mon, 15 Sep 2014 21:09:38 GMT

I've setup a source type and am currently ingesting our MySQL slow query logs.

To get Splunk to recognize new entries properly I've added the following to my props.conf found at http://answers.splunk.com/answers/13109/mysql-slow-query-log-parsing.html

 [mysqlslow]
 # Use LINE_BREAKER to segment events.  An event may start with either "Time:"" or "User@Host:".
 # Look for a semicolon (termination of previous event) followed by one of those items.
 # However, the very first entry won't have a ";" before it. Instead it is preceeded by "Argument".
 LINE_BREAKER = (?:;|Argument)(\n)(?:\# Time: |\# User@Host: )
 SHOULD_LINEMERGE = false
 TRUNCATE = 0

Now I need to have a field extraction for the query itself. The above mentioned page recommends

 EXTRACT-mysqlslow-query-line = \n(?<query>[^#].*)$

So that's not working at all for me. Splunk seems to be ignoring it completely.

Below is an example of what the log looks like. I need to abstract everything after the SET timestamp=1410815181;

Any help would be greatly appreciated.

# User@Host: db_probe[db_probe] @  [x.x.x.x]
# Query_time: 2.055869  Lock_time: 0.000081 Rows_sent: 6  Rows_examined: 2933112
SET timestamp=1410815181;
SELECT run_code, oid_job_log, log_path_file
FROM rcodb.job_log job
WHERE
       server = 'xxxxx'
       AND update_timestamp > now() - INTERVAL 30 MINUTE
       ORDER BY run_code;

Re: How to extract query from MySQL Slow Query logs?

sk314 — Mon, 15 Sep 2014 22:14:37 GMT

Try this in your transforms.conf:

[sql-log-times]
REGEX = Query_time:\s(\d+.\d+)\s+Lock_time:\s(\d+.\d+)\s+Rows_sent:\s(\d+)\s+Rows_examined:\s(\d+)
FORMAT = query_time::$1 lock_time::$2 rows_sent::$3 rows_examined::$4


[sql-queries]
REGEX = ((SELECT|INSERT|UPDATE)(?s).+?;)
FORMAT = sql_query::$1

Mention these two stanzas in the corresponding props.conf entry.

Re: How to extract query from MySQL Slow Query logs?

brandonpal — Tue, 16 Sep 2014 14:21:04 GMT

Unfortunately this won't really work for me as all queries don't start with Select, insert, update and so on. I could put all options there but there could always be new ones in the log that I was not aware of.

What I need is to be able to capture anything after "SET timestamp=1410815181;" With the understanding that the value for timestamp will always change.

Re: How to extract query from MySQL Slow Query logs?

sk314 — Tue, 16 Sep 2014 16:02:12 GMT

In that case, you can replace that with REGEX = SET timestamp=\d+;((?s).+?;)
FORMAT = sql_query::$1

(if SET timestamp line and SQL Query are not in the same line, please include a '\n' at the end of the timestamp statement in the regex)