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.
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.
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.
Post a Comment