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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...