4 Replies Latest reply: Feb 7, 2013 11:19 AM by 988417 RSS

    Application Page Auditing

    988417
      Hi guys

      I am pretty new to Oracle Application Express, but have experience in SQL (as a student).

      I have created an application. I would like to audit which users visit which pages (tabs). I have tested using the Apex_Activity_Log features built-in but I would like to create something myself...

      Reading and understanding what I know, I was going to use a trigger. I don't think this would be suitable. Firstly, not all pages have tables; for example the welcome page has a simple HTML region, welcoming the user to the application (there are a number of pages similar to this). Secondly, I am not interested in what the user has done on the page, rather what tabs the user has visited and when.

      I am struggling to understand how this will work, or if it's even possible. Many of the auditing examples I have researched focus on trigger table audits.

      Any help and guidance would be fantastic.

      Thanks

      Matt.
        • 1. Re: Application Page Auditing
          TexasApexDeveloper
          Why do you want to reinvent the wheel, or in this case how APEX does page visit auditing? Does it not provide the level of detail you need? Explaining WHY you want to do this might help to explain HOW to do what you want to do..

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: Application Page Auditing
            988417
            Hi Tony

            The reason: As I understand, and I am by no means an expert, the built-in logs 'audit' all activity. I practiced querying against this with some level of detail in the SQL workshop area - which I was happy!

            But, what I would like is to maybe report, or even better graph this information for users of the system, within the application itself - with a simple few clicks. I have tested reporting and graphing against a query and with no success (failure to load). As the activity logs are views and not tables - this creates problems (as I understand). Sorry for being a little vague in my original post.

            Thanks

            Matt
            • 3. Re: Application Page Auditing
              jwellsnh
              Hi Matt,

              Here is what I use to monitor activity for the current day; it may get you started.
              SELECT apex_user "User",
                     page_name "Page Name",
                     page_id "Page ID",
                     to_char (view_date, 'MM/DD/YYYY HH24:MI PM') "Date/Time Viewed",
                     apex_util.get_since(sysdate - (sysdate - view_date))
                        "Time Ago",
                     rows_queried "Rows Queried"
              FROM   apex_workspace_activity_log
              WHERE  (:p315_user = '%null%' OR apex_user LIKE '%' || :p315_user || '%')
                     AND trunc (view_date) = trunc (sysdate)
              I filter the report with a LOV based on this query:
              SELECT username d, username r
              FROM   ( SELECT DISTINCT apex_user username
                       FROM   apex_workspace_activity_log
                       WHERE  trunc( view_date ) = trunc( sysdate ) )
              ORDER BY 1
              Jeff
              • 4. Re: Application Page Auditing
                988417
                Hi Jeff

                I just wanted to thank-you for your reply. It's been a great help.

                Matt