Splunk Search

Tie multiline data

tven7
Path Finder

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.

Tags (3)
0 Karma

tven7
Path Finder

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

0 Karma

tven7
Path Finder

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

0 Karma

Ayn
Legend

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
Path Finder

that worked, thank you very much.

0 Karma

Ayn
Legend

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.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...