This discussion is archived
4 Replies Latest reply: Feb 7, 2013 9:19 AM by 988417 RSS

Application Page Auditing

988417 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Jeff

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

    Matt

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points