Storage and analysis of audit logs
Archiving the audit log moves the active audit log to an archive directory while the server begins writing to a new, active audit log. Later, you can extract data from the archived log into delimited files and then load data from these files into Db2® database tables for analysis.
Configuring the location of the audit logs allows you to place the audit logs on a large, high-speed disk, with the option of having separate disks for each member in a multiple member database environment, such as a Db2 pureScale environment or a partitioned database environment. In a multiple member database environment, the path for the active audit log can be a directory that is unique to each member. Having a unique directory for each member helps to avoid file contention, because each member is writing to a different disk.
db2audit configure datapath /auditlog archivepath /auditarchive
The
audit log storage locations you set using db2audit apply
to all databases in the instance.The path for active audit logs (datapath) in a multiple member database environment
- Use database member expressions when you specify the datapath parameter. Using database member expressions allows the member number to be included in the path of the audit log files and results in a different path on each database member.
- Use a shared drive that is the same on all members.
db2audit configure datapath '/pathForNode $N'
uses
the following paths:- /pathForMember10
- /pathForMember20
- /pathForMember30
Archiving active audit logs
The system administrator can use the db2audit tool to archive both instance and database audit logs as well as to extract audit data from archived logs of either type.
The security administrator, or a user to whom the security administrator has granted EXECUTE privilege on the audit routines, can archive the active audit log by running the SYSPROC.AUDIT_ARCHIVE stored procedure. To extract data from the log and load it into delimited files, they can use the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure.
- Schedule an application to perform regular archives of the active audit log using the stored procedure SYSPROC.AUDIT_ARCHIVE.
- Determine which archived log files are of interest. Use the SYSPROC.AUDIT_LIST_LOGS table function to list all of the archived audit logs.
- Pass the file name as a parameter to the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure to extract data from the log and load it into delimited files.
- Load the audit data into Db2 database tables for analysis.
If a problem occurs during archive, such as running out of disk space in the archive path, or the archive path does not exist, the archive process fails and an interim log file with the file extension .bk is generated in the audit log data path, for example, db2audit.instance.log.0.20070508172043640941.bk. After the problem is resolved (by allocating sufficient disk space in the archive path, or by creating the archive path) you must move this interim log to the archive path. Then, you can treat it in the same way as a successfully archived log.
Archiving active audit logs in a multiple member database environment
db2audit archive to /auditarchive
creates
the following files:- /auditarchive/db2audit.log.10.timestamp
- /auditarchive/db2audit.log.20.timestamp
- /auditarchive/db2audit.log.30.timestamp
- Use the node option with the db2audit command to perform the archive for the current member only.
- Use the db2_all command
to run the archive on all members. For example:
This sets the DB2NODE environment variable to indicate on which members the command is invoked.db2_all db2audit archive node to /auditarchive
- On member 10:
db2audit archive node 10 to /auditarchive
- On member 20:
db2audit archive node 20 to /auditarchive
- On member 30:
db2audit archive node 30 to /auditarchive
Example of archiving a log and extracting data to a table
CALL SYSPROC.AUDIT_ARCHIVE( '/auditarchive', -2 )
As part of their security procedures, the company has identified and defined a number of suspicious behaviors or disallowed activities that it needs to watch for in the audit data. They want to extract all the data from the one or more audit logs, place it in a relational table, and then use SQL queries to look for these activities. The company has decided on appropriate categories to audit and has associated the necessary audit policies with the database or other database objects.
CALL SYSPROC.AUDIT_DELIM_EXTRACT(
'', '', '/auditarchive', 'db2audit.%.200604%', '' )
In
another example, they can call the SYSPROC.AUDIT_DELIM_EXTRACT stored
procedure to extract the archived audit records with success events
from the EXECUTE category and failure events from the CHECKING category,
from a file with the timestamp they are interested in:CALL SYSPROC.AUDIT_DELIM_EXTRACT( '', '', '/auditarchive',
'db2audit.%.20060419034937', 'category
execute status success, checking status failure );