topic Re: Tie multiline data in Splunk Search

Tie multiline data

tven7 — Mon, 28 Sep 2020 09:54:33 GMT

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.

Re: Tie multiline data

Ayn — Fri, 23 Sep 2011 21:03:00 GMT

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.

Re: Tie multiline data

tven7 — Fri, 23 Sep 2011 22:21:29 GMT

that worked, thank you very much.

Re: Tie multiline data

tven7 — Mon, 28 Sep 2020 09:54:36 GMT

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? 🙂

Re: Tie multiline data

Ayn — Mon, 26 Sep 2011 07:46:13 GMT

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.

Re: Tie multiline data

tven7 — Mon, 28 Sep 2020 09:55:47 GMT

thank you it worked.