Tie multiline data

[tven7]

Hi,

We have a sql log where the format is not conducive to a predictable pattern for delimiting. Or so i think. In any case, i am interested in tying two lines together based on time stamp of the entry in the log.

These are two lines from the splunk search on the log file. The time in hh:mm:ss.microsecond format are the first line printed on each line as you all know.

9/23/11
1:34:03.000 PM
SET timestamp=1316810043;
show table status from `lportal`;

    host=xyz.acmexyz.com   Options|  
    sourcetype=xyz_MasterDB_SlowQuery   Options|  
    source=/var/lib/mysql/data/slow-queries.log   Options





9/23/11
    1:34:03.000 PM  
    # Time: 110923 13:34:03
    # User@Host: readonly[readonly] @  [172.20.6.1]
    # Thread_id: 13978257  Schema: lportal  Last_errno: 0  Killed: 0
    # Query_time: 6.795079  Lock_time: 0.000058  Rows_sent: 294  Rows_examined: 294  Rows_affected: 0  Rows_read: 294
    # Bytes_sent: 34241  Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0

Now i am interested in extracting Query time from #2nd set of events , which are upward of 1sec and associate it with the queries running during that Time.

I could easily extract time stamp as rex ="Query_time:\s(?P\d+.\d+)\s+"

Now i want to tie this extracted query_time and take its timestamps and extract the query from first set of events.

Any help is apreciated.

[tven7]

Did'nt create a seperate thread because its a continuation to the original QQ, but here is the next question in this query.

# Time: 110923 15:36:52
# User@Host: readonly[readonly] @  [172.20.6.1]
# Thread_id: 13984829  Schema: lportal  Last_errno: 0  Killed: 0
# Query_time: 6.230555  Lock_time: 0.000036  Rows_sent: 294  Rows_examined: 294  Rows_affected: 0  Rows_read: 294
# Bytes_sent: 34243  Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1316817412;
show table status from `lportal`;

base search|transaction _time|rex ="(?m)Query_time:\s+(?P\d+.\d+)\s+Lock_time.*?SET\stimestamp=\d+;(?P[^ ]+?);"

I was trying to extract Query_time and the query on the last line. And cant get it the extraction to work.

Since its tied together by transaction _time, can you assume you dont need a new line character when you go from line to line?

Any ideas how this can be achieved? 🙂

[tven7]

thank you it worked.

|transaction _time|rex "(?s)Query_time:\s+(?P\d+.\d+)\s+Lock_time.?SET\stimestamp=\d+;\n(?P.?);"|table masterdb_query_time masterdb_query|sort -masterdb_query_time|head 10

[Ayn]

No, there are still newline characters in there, so to make Splunk match that regex you need to tell it to have a dot match newlines by including (?s) at the start of the regex.

[tven7]

that worked, thank you very much.

[Ayn]

If the timestamps for the two events are 100% identical, you could run transaction on the _time field.

<yourbasesearch> | transaction _time

That'll tie the two events together so you can get both the query and the result stats in the same combined event.