Splunk Search

How to extract query from MySQL Slow Query logs?

brandonpal
Explorer

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;
0 Karma

sk314
Builder

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.

0 Karma

sk314
Builder

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)

0 Karma

brandonpal
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...