SELECT user1.user_name AS 'patroller name',

    user2.user_name AS 'rights granter/revoker',

    log_timestamp,

    (CASE

        WHEN log_params LIKE '%oldgroups%"patroller"%newgroups%' THEN 'revoked'

        WHEN log_params LIKE '%oldgroups%newgroups%"patroller"%' THEN 'granted'

        ELSE log_params

    END) AS 'action',

    comment_text, log_id

FROM user_former_groups

JOIN user AS user1

    ON ufg_user = user1.user_id

JOIN logging_logindex

    ON log_title = REPLACE(user1.user_name, ' ', '_')

    AND log_type = 'rights' AND log_action = 'rights'

JOIN user AS user2

    ON log_user = user2.user_id

JOIN comment

    ON log_comment_id = comment_id

WHERE ufg_group = 'patroller'

    -- exclude users who had patroller revoked at one time but are currently patroller again

    AND NOT EXISTS (SELECT 1

                    FROM user_groups

                    WHERE ug_user = ufg_user

                        AND ug_group = ufg_group)

    -- exclude rights grants where user didn't have patroller either before or after

    AND log_params LIKE '%"patroller"%'

    -- exclude rights grants where user had patroller both before and after

    AND log_params NOT LIKE '%oldgroups%"patroller"%newgroups%"patroller"%'

ORDER BY user1.user_name, log_timestamp;

​