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
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.
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
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? 🙂
thank you it worked.
|transaction _time|rex "(?s)Query_time:\s+(?P
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.
that worked, thank you very much.
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.