Skip to Content
avatar image
Former Member

Cleanup audit_log table data older than 30days

we have enabled audit in prod hana database and the audit table growing huge , so need to cleanup older than 30days records .

ways to follow ?

Thanks

Hakar

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jul 01, 2017 at 02:32 PM
    -3

    Hello,

    This CLEAR AUDIT LOG statement clears all audit_log table content.

    If you want to delete the old data before a certain date, use truncate button on Security, Auditing tab of HANA Studio.

    Regards,

    Yuksel AKCINAR

    Add comment
    10|10000 characters needed characters exceeded

    • Thank you Lars for the info.

      I have not seen this TIMESTAMP extension on training content.

      So we should always check help.sap.com for the currnet info.

      ALTER SYSTEM CLEAR AUDIT LOG Statement (System Management)

      Regards,

      Yuksel AKCINAR

  • Jul 01, 2017 at 05:41 AM

    You can do that with statement ALTER SYSTEM CLEAR AUDIT LOG.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 02, 2017 at 01:56 AM

    Some time ago I created a procedure for a customer who wanted to move the audit log data from the internal table to an external table in IQ.

    The idea is to have an external table (e.g. in IQ) and move audit entries into it before truncating the audit log. The version in the script works with a normal HANA table but you should be able to replace it with a table in IQ (or elsewhere).

    It also contains a view definition to read both the normal internal as well as the external table together - including the default behaviour that the view will be empty if the relevant system privileges are not granted.

    The archived audit log also contains information on the archiving itself, so that there always should be corresponding entries for all entries in the external audit log.

    This is provided as is. No support. No maintenance. I keep all rights on this though. :)

    /*
        Archive table definition
    */
    drop table "ADMIN"."AUDIT_LOG_ARCHIVE" ; 
    CREATE COLUMN TABLE "ADMIN"."AUDIT_LOG_ARCHIVE" (
         "ARCHIVE_TIMESTAMP" LONGDATE CS_LONGDATE NOT NULL,
         "ARCHIVE_DB_USER_NAME" NVARCHAR(256) NOT NULL ,
         "TIMESTAMP" LONGDATE CS_LONGDATE,
         "HOST" VARCHAR(64),
         "PORT" INTEGER CS_INT,
         "SERVICE_NAME" VARCHAR(32),
         "CONNECTION_ID" INTEGER CS_INT,
         "CLIENT_HOST" VARCHAR(64),
         "CLIENT_IP" VARCHAR(16),
         "CLIENT_PID" BIGINT CS_FIXED,
         "CLIENT_PORT" INTEGER CS_INT,
         "USER_NAME" NVARCHAR(256),
         "APPLICATION_USER_NAME" NVARCHAR(256),
         "AUDIT_POLICY_NAME" NVARCHAR(256),
         "EVENT_STATUS" VARCHAR(32),
         "EVENT_LEVEL" VARCHAR(16),
         "EVENT_ACTION" VARCHAR(40),
         "SCHEMA_NAME" NVARCHAR(256),
         "OBJECT_NAME" NVARCHAR(256),
         "PRIVILEGE_NAME" NVARCHAR(256),
         "ROLE_SCHEMA_NAME" NVARCHAR(256),
         "ROLE_NAME" NVARCHAR(256),
         "GRANTEE_SCHEMA_NAME" NVARCHAR(256),
         "GRANTEE" NVARCHAR(256),
         "GRANTABLE" VARCHAR(16),
         "FILE_NAME" VARCHAR(256),
         "SECTION" VARCHAR(128),
         "KEY" VARCHAR(128),
         "PREV_VALUE" VARCHAR(5000),
         "VALUE" VARCHAR(5000),
         "STATEMENT_STRING" NCLOB MEMORY THRESHOLD 1000,
         "COMMENT" VARCHAR(5000))
      UNLOAD PRIORITY 5 AUTO MERGE ;
      
    truncate table audit_log_Archive;
     
                                               
    /* make up testable dummy audit table */
    
    create column table caudit_log as (select * from audit_log);
    
    truncate table caudit_log;
    insert into caudit_log (select * from audit_log);
    grant select on admin.audit_log_archive to public;
    create public synonym audit_log_archive for admin.audit_log_archive;
    
    -- provide joined view
    
    
    create view all_audit_log 
        ( "TIMESTAMP", "HOST", "PORT", "SERVICE_NAME", 
          "CONNECTION_ID", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "CLIENT_PORT", 
          "USER_NAME", "APPLICATION_USER_NAME", 
          "AUDIT_POLICY_NAME", "EVENT_STATUS", "EVENT_LEVEL", "EVENT_ACTION", 
          "SCHEMA_NAME", "OBJECT_NAME", 
          "PRIVILEGE_NAME", "ROLE_SCHEMA_NAME", "ROLE_NAME", 
          "GRANTEE_SCHEMA_NAME", "GRANTEE", "GRANTABLE", 
          "FILE_NAME", "SECTION", "KEY", "PREV_VALUE", "VALUE", 
          "STATEMENT_STRING", "COMMENT"  )
    as (
        SELECT 
            "TIMESTAMP", "HOST", "PORT", "SERVICE_NAME", 
             "CONNECTION_ID", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "CLIENT_PORT", 
             "USER_NAME", "APPLICATION_USER_NAME", 
             "AUDIT_POLICY_NAME", "EVENT_STATUS", "EVENT_LEVEL", "EVENT_ACTION", 
             "SCHEMA_NAME", "OBJECT_NAME", 
              "PRIVILEGE_NAME", "ROLE_SCHEMA_NAME", "ROLE_NAME", 
             "GRANTEE_SCHEMA_NAME", "GRANTEE", "GRANTABLE", 
              "FILE_NAME", "SECTION", "KEY", "PREV_VALUE", "VALUE", 
             "STATEMENT_STRING", "COMMENT"
        FROM caudit_log
     union all
        SELECT 
              "TIMESTAMP", "HOST", "PORT", "SERVICE_NAME", 
              "CONNECTION_ID", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "CLIENT_PORT", 
              "USER_NAME", "APPLICATION_USER_NAME", 
              "AUDIT_POLICY_NAME", "EVENT_STATUS", "EVENT_LEVEL", "EVENT_ACTION", 
              "SCHEMA_NAME", "OBJECT_NAME", 
              "PRIVILEGE_NAME", "ROLE_SCHEMA_NAME", "ROLE_NAME", 
              "GRANTEE_SCHEMA_NAME", "GRANTEE", "GRANTABLE", 
              "FILE_NAME", "SECTION", "KEY", "PREV_VALUE", "VALUE", 
              "STATEMENT_STRING", "COMMENT"
        FROM audit_log_archive
        WHERE 
            -- use the sysprivileges for access check 
              1 = (SELECT  HASSYSTEMPRIVILEGE (CURRENT_USER, 'AUDIT ADMIN') FROM DUMMY) 
           OR 1 = (SELECT  HASSYSTEMPRIVILEGE (CURRENT_USER, 'AUDIT OPERATOR') FROM DUMMY)
    ) WITH READ ONLY;
    
    grant select on admin.all_audit_log to public;
    create public synonym all_audit_log for admin.all_audit_log;
    
    
    
    
    /*
     alter system clear audit log until '2016-05-01 00:00:00'
    */
    
    /* Compare statement */
    SELECT ts_day, sum(al_cnt) as al_cnt , sum(arc_cnt) as arc_cnt 
    FROM ( 
        select to_varchar(timestamp, 'DD.MM.YYYY HH24') ts_day, count(*) al_cnt, 0 arc_cnt
        from caudit_log
        group by to_varchar(timestamp, 'DD.MM.YYYY HH24')
    UNION ALL 
        select to_varchar(timestamp, 'DD.MM.YYYY HH24') ts_day, 0 al_cnt, count(*) arc_cnt
        from audit_log_archive
        where audit_policy_name != 'AuditArchiveBuiltinPolicy'
        group by to_varchar(timestamp, 'DD.MM.YYYY HH24'))
    GROUP BY ts_day
    ORDER BY ts_day ASC;    
    
    
    /*
    TS_DAY          AL_CNT  ARC_CNT
    04.05.2016 04   85      0      
    04.05.2016 03   339     0      
    03.05.2016 11   5       0      
    03.05.2016 09   66      0      
    03.05.2016 08   35      0      
    03.05.2016 07   689     0      
    03.05.2016 06   23,385  0      
    03.05.2016 05   57,713  0      
    03.05.2016 04   18,176  0      
    03.05.2016 02   1       0      
    03.05.2016 01   44      0      
    02.05.2016 09   504     504    
    02.05.2016 08   541     541    
    02.05.2016 06   182     182    
    02.05.2016 05   166     166    
    */
    
    
    /* write into arthive log table*/
    -- message into the audit_log_archive
    -- with some runtime data
    -- EVENT_ACTION and COMMENT can be used to insert additional informatio
    -- EVENT_STATUS can maybe be used, too
    
    insert into audit_log_archive 
                (ARCHIVE_TIMESTAMP, ARCHIVE_DB_USER_NAME, TIMESTAMP, 
                HOST, PORT, CONNECTION_ID,  CLIENT_HOST, CLIENT_IP, CLIENT_PID,  USER_NAME, 
                APPLICATION_USER_NAME, 
                AUDIT_POLICY_NAME, 
                EVENT_STATUS, 
                EVENT_LEVEL, 
                EVENT_ACTION, 
                COMMENT)
        (select 
                current_timestamp, session_user, current_timestamp, 
                c.host, c.port, c.connection_id, c.client_host, c.client_ip, c.client_pid,  c.user_name, 
                session_user as APPLICATION_USER_NAME,
                'AuditArchiveBuiltinPolicy' as AUDIT_POLICY_NAME, 
                'SUCCESSFUL' as EVENT_STATUS, 
                'CRITICAL' as EVENT_LEVEL,
                'COPY AUDIT LOG EVENTS TO ARCHIVE' as EVENT_ACTION,
                'COPIED 423 ENTRIES FOR TIMESLICE 03.05.2016 01' as COMMENT
             from 
                M_CONNECTIONS c
              where own='TRUE');
    
    
    /*
        Ok, the plan for the audit entries is:
        1. find all the entries older than 1 month
        2. group them by date-hour
        3. loop over all groups and copy them
        4. once copied
        5. group data in both the log table and the copy and count records
        6. where records are equal: delete group  & COMMIT
    
    This two phase, chunk wise approach should guarantee:
        1) all data is actually copied before getting deleted
        2) the data copy and deletion part are separate
        3) the deletion commits after a days worth of data. 
           this should allow the mergedog to work on the audit table
                                    
    */
    
    drop procedure  copy_and_delete_audit_log_by_age;
    create procedure copy_and_delete_audit_log_by_age (IN older_than_days SMALLINT)
    language SQLSCRIPT 
    SQL SECURITY INVOKER
    AS
    begin SEQUENTIAL EXECUTION
    DECLARE curr_run_timestamp timestamp := current_timestamp;
    -- find the relevant entries
    DECLARE cursor audit_slices for
                 select to_varchar(timestamp, 'DD.MM.YYYY HH24') ts_day, count(*) 
                 from audit_log
                 where to_date("TIMESTAMP") <= add_days(current_date, -1 * ( :older_than_days))
                 group by to_varchar(timestamp, 'DD.MM.YYYY HH24')
                 order by to_varchar(timestamp, 'DD.MM.YYYY HH24') ASC;
                 
    
    -- error handling
    DECLARE audit_priv int :=0;
    DECLARE errCode INT; 
    DECLARE errMsg VARCHAR(5000); 
    DECLARE noAuth CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION  
            BEGIN AUTONOMOUS TRANSACTION 
                  errCode= ::SQL_ERROR_CODE;
                  errMsg=  ::SQL_ERROR_MESSAGE ; 
                  
                  insert into audit_log_archive 
                                (ARCHIVE_TIMESTAMP, ARCHIVE_DB_USER_NAME, TIMESTAMP, 
                                HOST, PORT, CONNECTION_ID,  CLIENT_HOST, CLIENT_IP, CLIENT_PID,  USER_NAME, 
                                APPLICATION_USER_NAME, 
                                AUDIT_POLICY_NAME, 
                                EVENT_STATUS, 
                                EVENT_LEVEL, 
                                EVENT_ACTION, COMMENT)
                    (select 
                            current_timestamp, session_user, current_timestamp, 
                            c.host, c.port, c.connection_id, c.client_host, c.client_ip, c.client_pid,  c.user_name, 
                            session_user as APPLICATION_USER_NAME,
                            'AuditArchiveBuiltinPolicy' as AUDIT_POLICY_NAME, 
                            'UNSUCCESSFUL' as EVENT_STATUS, 
                            'CRITICAL' as EVENT_LEVEL,
                            'ERROR CODE '|| :errCode as EVENT_ACTION,
                            'Called with older_than_days: '|| :older_than_days ||
                                              ', error message: '|| :errMsg as COMMENT
                 from 
                    M_CONNECTIONS c
                  where own='TRUE');
             END; -- Auton. Transaction/error handling
    
    -- check for authorizaton now
        select greatest( (SELECT  HASSYSTEMPRIVILEGE (CURRENT_USER, 'AUDIT ADMIN') FROM DUMMY) 
                       , (SELECT  HASSYSTEMPRIVILEGE (CURRENT_USER, 'AUDIT OPERATOR') FROM DUMMY)) into audit_priv
        from dummy;
        
        if (audit_priv < 1) then   
            SIGNAL SQL_ERROR_CODE 10001;
        end if;
    
    -- for every slice, copy the data
        FOR curr_slice AS audit_slices DO
           insert into audit_log_archive 
                (ARCHIVE_TIMESTAMP, ARCHIVE_DB_USER_NAME,
                "TIMESTAMP", "HOST", "PORT", "SERVICE_NAME", 
                "CONNECTION_ID", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "CLIENT_PORT", 
                "USER_NAME", "APPLICATION_USER_NAME", 
                "AUDIT_POLICY_NAME", "EVENT_STATUS", "EVENT_LEVEL", "EVENT_ACTION", 
                "SCHEMA_NAME", "OBJECT_NAME", 
                "PRIVILEGE_NAME", "ROLE_SCHEMA_NAME", "ROLE_NAME", 
                "GRANTEE_SCHEMA_NAME", "GRANTEE", "GRANTABLE", 
                "FILE_NAME", "SECTION", "KEY", "PREV_VALUE", "VALUE", 
                "STATEMENT_STRING", "COMMENT")
           (select 
                :curr_run_timestamp, session_user,
                "TIMESTAMP", "HOST", "PORT", "SERVICE_NAME", 
                "CONNECTION_ID", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "CLIENT_PORT", 
                "USER_NAME", "APPLICATION_USER_NAME", 
                "AUDIT_POLICY_NAME", "EVENT_STATUS", "EVENT_LEVEL", "EVENT_ACTION", 
                "SCHEMA_NAME", "OBJECT_NAME", 
                "PRIVILEGE_NAME", "ROLE_SCHEMA_NAME", "ROLE_NAME", 
                "GRANTEE_SCHEMA_NAME", "GRANTEE", "GRANTABLE", 
                "FILE_NAME", "SECTION", "KEY", "PREV_VALUE", "VALUE", 
                "STATEMENT_STRING", "COMMENT"
            from 
                  audit_log 
            where 
                 to_varchar(timestamp, 'DD.MM.YYYY HH24') = curr_slice.ts_day);           
    
            call audit_archive_logger (event_status => 'SUCCESFUL', 
                                       EVENT_ACTION => 'COPY EVENTS TO ARCHIVE TABLE', 
                                       COMMENT=> 'Called with older_than_days: '|| :older_than_days ||
                                                 ' copied slice: '|| curr_slice.ts_day );
     
        END FOR;
        
    -- for every slice, delete the data and commit
        BEGIN 
        DECLARE delete_until NVARCHAR(20) :='YYYY-MM-DD HH:MI:SS';
        DECLARE ts_day NVARCHAR(50)   :=lpad (' ',  50) ;
        DECLARE command NVARCHAR (150):=lpad (' ',  150) ;
        
            FOR curr_slice AS audit_slices DO
                -- convert slice date into the format that CLEAR AUDIT understands...  
                ts_day := curr_slice.ts_day;
                
                -- build clear audit command
                -- in order to delete the whole hour range e.g. for 02.05.2015 09 
                -- we need to delete every record smaller or equal 02.05.2015 09:59:59
                -- so let's add 59 min and 59 seconds
    
                
                delete_until :=  to_varchar (add_seconds (to_timestamp (:ts_day , 'DD.MM.YYYY HH24'), (60*59)+59  )
                                            , 'YYYY-MM-DD HH24:MI:SS');
                command :=  'ALTER SYSTEM CLEAR AUDIT LOG UNTIL ''' || :delete_until  ||''' ';
                execute immediate :command;
                
                -- for testing case
                --delete from caudit_log where "TIMESTAMP" <=  add_seconds (to_timestamp (:ts_day , 'DD.MM.YYYY HH24'), (60*59)+59 );
                --commit; 
               
               call audit_archive_logger (event_status => 'SUCCESFUL', 
                                          EVENT_ACTION => 'CLEAR AUDIT LOG', 
                                          COMMENT=> :command);
            END FOR;
           
            
        END;                           
    ---    
            
    end;
    
    grant execute on copy_and_delete_audit_log_by_age to devdude;
    call copy_and_delete_audit_log_by_age (0);
    --
    -- warning but no error 
    -- java.sql.SQLWarning: Not recommended feature: System control statement is used in Dynamic SQL (current dynamic_sql_sys_ctrl_error_level = 1)
    
    alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'dynamic_sql_sys_ctrl_error_level') = 'silent' with reconfigure
    
    drop procedure audit_archive_logger;
    create procedure audit_archive_logger (IN EVENT_STATUS NVARCHAR(32), 
                                           IN EVENT_ACTION NVARCHAR(40), 
                                           IN COMMENT NVARCHAR)
    language SQLSCRIPT
    as
    BEGIN
    
        insert into audit_log_archive 
                        (ARCHIVE_TIMESTAMP, ARCHIVE_DB_USER_NAME, TIMESTAMP, 
                        HOST, PORT, CONNECTION_ID,  CLIENT_HOST, CLIENT_IP, CLIENT_PID,  USER_NAME, 
                        APPLICATION_USER_NAME, 
                        AUDIT_POLICY_NAME, 
                        EVENT_STATUS, 
                        EVENT_LEVEL, 
                        EVENT_ACTION, COMMENT)
            (select 
                    current_timestamp, session_user, current_timestamp, 
                    c.host, c.port, c.connection_id, c.client_host, c.client_ip, c.client_pid,  c.user_name, 
                    session_user as APPLICATION_USER_NAME,
                    'AuditArchiveBuiltinPolicy' as AUDIT_POLICY_NAME, 
                    :EVENT_STATUS as EVENT_STATUS, 
                    'CRITICAL' as EVENT_LEVEL,
                    :EVENT_ACTION as EVENT_ACTION,
                    :COMMENT as COMMENT
             from 
                M_CONNECTIONS c
              where own='TRUE');
        
            COMMIT;
    
    END;
    
    
    call audit_archive_logger (event_status => 'SUCCESFUL', 
                                       EVENT_ACTION => 'CLEAR AUDIT LOG', 
                                COMMENT=>'this is just a comment');
    
    select top 5 * from archive_audit_log order by timestamp desc;
    
    
    ----
    
    
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Great one Lars, its better to dump the data of the audit into some place rather truncating it.

  • avatar image
    Former Member
    Jul 13, 2017 at 11:31 AM

    Hi,

    You can use ALTER SYSTEM CLEAR AUDIT LOG Statement which deletes old audit data from the SAP HANA database audit table.

    Syntax

    ALTER SYSTEM CLEAR AUDIT LOG <until_specification>

    Syntax Elements

    <until_specification> - Specifies to remove audit data older than the <timestamp>.

    Best Regards,

    Thiru

    Add comment
    10|10000 characters needed characters exceeded