I have found many answers to similar questions but none of them is solving my problem.
I try to explain: I have logfiles generated by mysql general log in which i want to filter the queries fired on a certain table. I'm trying to do this by grep and regex.
Each entry starts with a double tab. So i enabled multiline and dotall and tried to get the entries by positive lookbehind and positive lookahead (?<=\t\t).*?(?=\t\t|\Z)
.
This is working so far, also for queries spread over multiple lines.
What i'm not able to do is filter out only the entries for a certain table (Table name: s_order).
(?<=\t\t).*?s_order.*?(?=\t\t|\Z)
is not working. It seems logic as the match pattern in the middle includes any kind of char before the table name and any kind after it. But i cant come up with an working solution.
Example:
In front of each 8-digit number there is a double tab, so the pasted section below is not formatted correctly. There is also a tab after the keywords Query, Quit, Connext, ...
e.g. (tab)(tab)36003847 Query(tab)SELECT count(userid) AS number FROM sys_user WHERE typ = 'admin'
What i want in my result is line 4 and lines 8 to 15.
36003847 Query SELECT count(userid) AS number FROM sys_user WHERE typ = 'admin'
36003847 Quit
36003844 Query SELECT * FROM server_php WHERE server_id = 1 AND name = 'PHP 5.4.23' AND php_fpm_init_script = '/etc/init.d/php-5.4.23-fpm'
36003844 Query UPDATE s_order SET php_version = '5.4.23' WHERE server_php_id = '3'
36003848 Connect c1Live@localhost as anonymous on c1LIVE
36003848 Query SET NAMES 'utf8'
36003848 Query SET @@session.sql_mode = ''
36003848 Query SELECT
name, id, name, label,
description, source, active,
installation_date as installationDate,
update_date as updateDate, changes,
version
FROM s_order
WHERE namespace='Core'
36003848 Query SELECT
ce.subscribe as name,
ce.listener,
ce.position,
cp.name as plugin
FROM s_core_subscribes ce
JOIN s_core_plugins cp
ON cp.id=ce.pluginID
AND cp.active=1
AND cp.namespace='Core'
WHERE ce.type=0
ORDER BY name, position