Skip to main content

Activity Log Exploration Queries

Queries to explore and analyze the activity_log table in Farfalla.

Count by Log Name​

SELECT
    log_name,
    COUNT(*)
FROM
    activity_log
GROUP BY
    log_name;

Distinct Log Names Sorted by Volume​

SELECT DISTINCT
    log_name,
    COUNT(*)
FROM
    activity_log
GROUP BY
    1
ORDER BY
    2 DESC;

Default Log Activities​

SELECT
    log_name,
    description,
    COUNT(*)
FROM
    activity_log
WHERE log_name = "default"
GROUP BY
    description;

Updated Activities by Subject Type​

SELECT
    log_name,
    description,
    subject_type,
    COUNT(*)
FROM
    activity_log
WHERE log_name = "default" and description = "updated"
GROUP BY
    subject_type;

Order Update Logs (High Volume)​

Note: Order update logs are the most common entries in the activity_log table. This query helps identify old records for potential cleanup to keep table size under control.

SELECT * FROM `farfalla`.`activity_log` 
WHERE (`subject_type` = 'App\\Domains\\Commerce\\Models\\Order') 
AND (`description` = 'updated') 
AND (`log_name` = 'default') 
AND (`created_at` < '2022-12-01') 
LIMIT 3500 OFFSET 0;

PDF Issue Conversion Debugging​

Trace the conversion process for a specific PDF issue, ordered by conversion steps.

SELECT
    *
FROM
    `activity_log`
WHERE
    `subject_type` = 'issue'
    AND `subject_id` = 1125899955000001
--     and description LIKE "%workerRequest%"
ORDER BY
    `created_at` DESC,
    CASE 
        WHEN `log_name` = 'issues_conversion_process' 
        THEN CAST(REGEXP_SUBSTR(`description`, '^[0-9]+') AS UNSIGNED) 
        ELSE NULL 
    END DESC,
    id DESC
LIMIT 8000 OFFSET 0;
X

Graph View