Skip to Content
0

Cleanup audit_log table data older than 30days

Jun 30, 2017 at 07:24 PM

782

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Yuksel AKCINAR 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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

The CLEAR AUDIT LOG command does take a timestamp parameter to limit what entries to delete.

0

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

0
Florian Pfeffer
Jul 01, 2017 at 05:41 AM
0

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

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Jul 02, 2017 at 01:56 AM
0

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;


----




Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

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

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

Share
10 |10000 characters needed characters left characters exceeded