0

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
tripleee
  • 175,061
  • 34
  • 275
  • 318
mkreis
  • 11
  • 5
  • 1
    Sounds like you are looking at the wrong tool. Write a simple Awk parser for this format and take it from there. – tripleee Sep 08 '20 at 08:44
  • You may try `(?<=\t\t)(?:(?!\t\t).)*?s_order.*?(?=\t\t|\Z)` but it might not work for you since your multichar left and right delimiters seem to be the same, double tabs. – Wiktor Stribiżew Sep 08 '20 at 08:45
  • @WiktorStribiżew this is working, as far as i have tested yet. Many thanks! – mkreis Sep 09 '20 at 09:30
  • @tripleee if i run into further problems, i will have a look at awk. certainly i will have it in the back of my mind for the future. Thanks. – mkreis Sep 09 '20 at 09:30

0 Answers0