All Apps and Add-ons

Splunk Add-on for MySQL: How do I deal with truncated MySQL General logs that cause issues with time format and line breaking?

mcrawford44
Communicator

Using the following time format from props.conf included with Splunk MySQL TA;

TIME_FORMAT = %y%m%d %H:%M:%S

Used to split the following log format by timestamp;

   150803  7:27:03  102983 Connect  drupal@foo.com on bar
            102983 Query    select @@version_comment limit 1
            102983 Query    SELECT whatever from whatever
            102983 Quit 

Creates an event for 08/03/15 7:27:03 correctly. However, when an event in this log is truncated like so:

             102983 Query    select @@version_comment limit 1
             102983 Query    SELECT whatever from whatever
             102983 Quit 

Splunk reads the event ID as the timestamp instead as 10/29/2015 8:42:02 and groups the next 150+ lines including other events that should be split by timestamp.

Is there a way to account for these truncated log entries? The MySQL host in question is using all default settings.

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

I'd experiment with the SHOULD_LINEMERGE=true options on this page http://docs.splunk.com/Documentation/Splunk/6.2.2/Data/Indexmulti-lineevents

I think BREAK_ONLY_BEFORE or BREAK_ONLY_AFTER might do it, depends on the data though.

0 Karma

mcrawford44
Communicator

The sourcetype appears to take that into account already. I can't figure out why events are getting the wrong timestamp, and are being combined. There are two artifacts occurring and I can not seem to replicate them.

props.conf

    [mysql:generalQueryLog]
    KV_MODE = multi_mysql_query_log
    TRUNCATE = 0
    TIME_FORMAT = %y%m%d %H:%M:%S
    SHOULD_LINEMERGE = true
    BREAK_ONLY_BEFORE = ^(\d{6})\s
    EXTRACT-login_success = Connect\s*(?<user>[^@]+)@(?<client_host>\S+)
    EVAL-action = case(isnotnull(client_host), "success")
    EVAL-Id = case(isint(Id), Id)
    MAX_TIMESTAMP_LOOKAHEAD = 128

This is what an event should look like with a timestamp of 08/03/2015 4:42:02;

150803  4:42:02 102917 Connect  drupal@server.com on whatever
        102917 Query    select @@version_comment limit 1
        102917 Query    SELECT * FROM whatever WHERE foo Or bar

This is artifact #1 (Wrong timestamp, truncation of event start) that shows a timestamp of 10/29/2015 8:42:02;

            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:44:08 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:47:12 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar

Artifact #2 ( wrong timestamp, and failure to break event ) that shows a timestamp of 10/29/2015 8:42:02;

    150803  4:42:02 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:43:02 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:44:02 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:45:02 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
    150803  4:42:02 102917 Connect  drupal@server.com on whatever
            102917 Query    select @@version_comment limit 1
            102917 Query    SELECT * FROM whatever WHERE foo Or bar
0 Karma

mcrawford44
Communicator

It looks like this line is breaking on any 6 digits;

BREAK_ONLY_BEFORE = ^(\d{6})\s

I'm trying this;

BREAK_ONLY_BEFORE = ^(\d{6})\s+\d+:\d+:\d+
0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...