Tracking developers activity

T

There are some interesting views in APEX dictionary which can help you to track users/developers activity.

SELECT * FROM apex_workspace_access_log     ORDER BY access_date DESC;
SELECT * FROM apex_workspace_activity_log   ORDER BY view_timestamp DESC;
SELECT * FROM apex_debug_messages           ORDER BY message_timestamp DESC;

For this purpose I not interested in login attempts, so I can skip the access log.

Developers activity

To track activity of developers in you app you can use following query. It includes both activity in the current app itself and in the APEX Builder for the same workspace. It also transform developers account name into user name. You can distinguish APEX Builder from your app via the application_id or by empty application_name. Request_type "R" means rendering of the page, "P" means processing (page submit).

You can use same query without the LEFT JOIN to track common app users.

SELECT
    NVL(d.user_id, LOWER(l.apex_user))  AS user_id,
    l.application_id,
    l.application_name,                 -- NULL for APEX Builder
    l.page_id,
    l.page_name,
    SUBSTR(l.page_view_type, 1, 1)      AS request_type,
    l.request_value,
    l.view_timestamp                    AS requested_at
FROM apex_workspace_activity_log l
JOIN apex_workspaces w
    ON w.workspace_id                   = l.workspace_id
JOIN apex_applications a
    ON a.workspace                      = w.workspace
    AND a.application_id                = l.application_id
LEFT JOIN (
    SELECT
        UPPER(d.user_name)              AS user_name,
        LOWER(d.email)                  AS user_id
    FROM apex_workspace_developers d
    WHERE d.is_application_developer    = 'Yes'
        AND d.account_locked            = 'No'
) d
    ON d.user_name                      = l.apex_user
WHERE a.application_id                  = NVL(NV('APP_ID'), a.application_id)
    AND l.page_view_type                IN ('Rendering', 'Processing', 'Ajax')
    AND l.apex_user                     NOT IN ('nobody')
ORDER BY l.view_timestamp DESC;

You can track users activity even if the APEX logging is disabled. Do you know how?

And you can use time bucket trick to generate nice chart from it.

Debug messages

You can also check debug messages to see if the developer is struggling with something. Or you can use following script to create a region in Footer on page zero, so you can see your own messages for each page without the need to opening debug window. Don't forget to set the Security of this region to developers only.

SELECT
    d.id,
    d.message_level,
    d.message,
    d.call_stack,
    d.message_timestamp                 AS created_at
FROM apex_debug_messages d
LEFT JOIN (
    SELECT
        UPPER(d.user_name)              AS user_name,
        UPPER(d.email)                  AS user_id
    FROM apex_workspace_developers d
    WHERE d.is_application_developer    = 'Yes'
        AND d.account_locked            = 'No'
        AND V('APP_USER')               IN (UPPER(d.user_name), UPPER(d.email))
) u
    ON d.apex_user                      IN (u.user_name, u.user_id)
WHERE d.application_id                  = NV('APP_ID')
    AND d.page_id                       = NV('APP_PAGE_ID')
    AND d.session_id                    = V('APP_SESSION')
ORDER BY d.message_timestamp DESC;

To force logging these debug messages you can use APEX_DEBUG package, specifically MESSAGE (with p_force => TRUE), ERROR or ENABLE procedures. I highly recommend to explore this package.