Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Exclamation Unanswered: Problems with generating Audit SQL

    Im currently working on a project that uses Oracle 10g where every action from the user is recorded in a auditlog table. The current table holds the following values:

    Code:
    AuditID
    AuditDate
    Username
    Page Name
    Type (Insert, Delete, Update, Login etc...)

    Whenever a user does one of the above Types an audit log is saved.

    What I would like to do is to get a count of each page for a particular time scale. For example Day, Month, Year and Quarter.

    I have came up with some SQL to get the results for all of the pages together but i am having some trouble with getting the values for each individual page. Below i have given one of the SQL queries that i am using (Year).

    Code:
    SELECT (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-1100, 'yy')) As Y_2009,
    (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-732, 'yy')) As Y_2010,
    (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-368, 'yy')) As Y_2011,
    (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate), 'yy')) As Y_2012,
    (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)+368, 'yy')) As Y_2013
    From Dual;
    This SQL would give me the results of every single page for each year. However I need to find out the results for each page for each year. Is there anyway of doing this without creating an SQL statement like the one above for each page?

    If you would like more information please ask.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What I would like to do is to get a count of each page for a particular time scale. For example Day, Month, Year and Quarter.

    what exactly do you consider to be a "page"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    Within my auditlog table there is a pagename column. This contains the name of each page that the audit took place on for example... Login.asp, home.asp etc etc. This is what i class as Page

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try it with the 'group by' clause:
    Code:
    select
    ( select page_name, count(*) from auditlog where to_char( auditdate, 'yy' ) = to_char( to_date( sysdate ) -1100, 'yy' ) group by page_name ) as y_2009,
    ( select page_name, count(*) from auditlog where to_char( auditdate, 'yy' ) = to_char( to_date( sysdate ) -732,  'yy' ) group by page_name ) as y_2010,
    ( select page_name, count(*) from auditlog where to_char( auditdate, 'yy' ) = to_char( to_date( sysdate ) -368,  'yy' ) group by page_name ) as y_2011,
    ( select page_name, count(*) from auditlog where to_char( auditdate, 'yy' ) = to_char( to_date( sysdate ),       'yy' ) group by page_name ) as y_2012,
    ( select page_name, count(*) from auditlog where to_char( auditdate, 'yy' ) = to_char( to_date( sysdate ) +368,  'yy' ) group by page_name ) as y_2013
    from dual;

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    @spacebar: The query you proposed will fail as scalar subquery can return one column and at most one row. Your one violates both restrictions.

    I wonder what is wrong with standard pivotting as described e.g. here: http://www.oracle-base.com/articles/...tors-11gr1.php, so it would be simple:
    Code:
    select page_name,
      count( case when <condition1> then 1 end) <whatever describing condition1>,
      count( case when <condition2> then 1 end) <whatever describing condition2>,
      ...
    from audit_log
    group by page_name;
    Two remarks to given code:
    TO_DATE( SYSDATE ) is a bug - as TO_DATE does not support parameter with DATE data type, two implicit conversions are made and the only result is truncating it to the least date/time portion present in NLS_DATE_FORMAT mask.
    SYSDATE+368 - you are aware it will return year 2014 on December, 31st 2012? If you insist on "dynamic" obtaining of year values (by the way, column aliases are not), just use ADD_MONTHS function; alternatively EXTRACT function returns NUMBER data type, so you may simply add/subtract required number of years. And, finally, year has four digits. Do not be lazy and use them all.

  6. #6
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Yep my 'bad' , I did NO testing, should have pointed him there and gave some examples:
    Code:
    --Oracle 11.2 pivot example to generate the SQL to copy/paste/run or use execute immediate:
    select 'select *
             from  ( select owner, count(*) as owner_segment_count
                      from  dba_segments
            group by owner )
            pivot ( sum( owner_segment_count ) for owner in (' ||
            listagg ( '''' || owner || '''', ',' ) within group ( order by owner ) || '))'
     from  ( select distinct owner
              from  dba_segments )
    --------------------------------------------------------------------------------------------------------------------------------
    --On Oracle 11 but not on Oracle 11.2 yet, pivot example to generate the SQL to copy/paste/run or use execute immediate:
    with
    sql_stmt as(
      select 'select *
               from  ( select owner, count(*) as owner_segment_count
                        from  dba_segments
                       group by owner )
               pivot ( sum( owner_segment_count ) for owner in (' as sql
       from dual ),
    pivot_data as(
      select replace( rtrim( xmlagg( xmlelement( c,owner, ',' ).extract( '//text()' ) ), ',' ), '''', '''') || ' ) )' as pivot_data
       from  ( select distinct '''' || owner || '''' as owner
                from  dba_segments
               order by owner ) )
    select sql || replace( pivot_data, '&apos;', '''' )
     from  sql_stmt join pivot_data on sql != pivot_data

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •