Joining log lines with sed

It’s often the case when analysing system logs that you want to create a summary bringing together data from different lines of a log – for example capturing an initial and final state of a transaction, or (as in this example) capturing a date-time stamp and some data from a multi-line log entry.

In this example I have a log file containing periodic extracts of ‘mysqladmin extended-status’ with a date-time line to record when the status was taken – for example (removing most of the lines with “…” for brevity):

========================= Tue Sep 12 06:00:01 BST 2017 (1505192401) ====================
+--------------------------------+---------------+
| Variable_name | Value |
+--------------------------------+---------------+
| Aborted_clients | 42096 |
| Aborted_connects | 38094 |
| Binlog_cache_disk_use | 0 |
...
+--------------------------------+---------------+
========================= Tue Sep 12 06:03:04 BST 2017 (1505192584) ====================
+--------------------------------+---------------+
| Variable_name | Value |
+--------------------------------+---------------+
| Aborted_clients | 42096 |
| Aborted_connects | 38095 |
| Binlog_cache_disk_use | 0 |
...
+--------------------------------+---------------+
========================= Tue Sep 12 06:06:01 BST 2017 (1505192761) ====================
+--------------------------------+---------------+
| Variable_name | Value |
+--------------------------------+---------------+
| Aborted_clients | 42096 |
| Aborted_connects | 38096 |
| Binlog_cache_disk_use | 0 |
...
+--------------------------------+---------------+

I’d like an extract of “Threads_connected” lines with the associated date-time stamps – which I can do with sed quite easily. The following example is much longer than strictly necessary because it includes comments about what is being done, and it also removes some stuff from the output to make it more readable:

sed -n '
 # Timestamp line: overwrite/store in hold space
 /^===/h;
 # Threads_connected line
 /Threads_connected/{
 ## Exchange pattern <-> hold space and then append this line
 x;G;
 ## Remove newline
 s/\n/ /;
 ## Remove excess stuff from the line and print it
 s/=*//g;
 s/^ *//;
 s/([0-9]*) //;
 s/ */ /g;p
 }' mysql-status/*

Which produces the following output:

...
Tue Sep 12 07:09:01 BST 2017 | Threads_connected | 117 |
Tue Sep 12 07:12:01 BST 2017 | Threads_connected | 121 |
Tue Sep 12 07:15:05 BST 2017 | Threads_connected | 110 |
Tue Sep 12 07:18:01 BST 2017 | Threads_connected | 117 |
Tue Sep 12 07:21:01 BST 2017 | Threads_connected | 113 |
Tue Sep 12 07:24:02 BST 2017 | Threads_connected | 94 |
Tue Sep 12 07:27:01 BST 2017 | Threads_connected | 105 |
Tue Sep 12 07:30:01 BST 2017 | Threads_connected | 118 |
...

The previous example can be thrown together which much less typing for on-to-fly analysis. It’s possible to get the data needed without the nice formatting and commenting shown above using:

sed -n '/^=/h;/Threads_connected/{x;G;s/\n/ /;p}' mysql-status/*

Leave a Reply

Your email address will not be published. Required fields are marked *