50 DB2 Nuggets #34 : Tech Tip - Using db2audit utility
Swati Thorve 270004FTYS Visits (7032)
Recently I had opportunity to play around db2audit tool. db2audit tool is DBA's best friend when it comes find all the activities going on in database. DB2® database systems provide an audit facility to assist in the detection of unknown or unanticipated access to data. The DB2 audit facility generates and permits the maintenance of an audit trail for a series of predefined database events.
For more information about db2audit command, refer Information Center
db2audit can be divided in 5 main steps as :
5> Stop (optional)
Today, I will share the steps I followed to work with db2audit.
1> Configure db2audit.
1.a> Before configuring db2audit, when I checked the status of my db2audit utility with db2audit describe, this is how it looked:
1.b> Now I went ahead and configured db2audit:
c:\temp>db2audit configure scope all status both errortype audit
I used the simplest form, I configured audit with scope 'all' and errortype 'audit', i.e I would like my audit log to capture all the events with errortype 'audit'. You can further refer db2audit command's 'Audit Configuration' section, to find what all events can be monitored. We can choose the path where audit data can get generated and where we would like archived audit logs to be stored, in configure command. You can use 'datapath' and 'archivepath' options for this respectively. I did not specify them, resulting audit logs and archive logs getting created in default location. For Windows it is C:\P
2> Start db2audit
Once db2audit is configured to capture the required events, you can start it.
db2audit describe should show us that db2audit is started and what all events it is capturing:
3> Generate audit data
Our db2audit is actively capturing all the events. I purposely tried logging to database with wrong password to check what all things we can find from audit log:
c:\temp>db2 connect to sample user db2admin using wrongpw
db2audit flush, will flush in memory audit data to on disk audit log.
if you check your audit log, you can see below two files created there now:
4> Archive db2audit log:
You can refer to section 'Audit Log Archive' of db2audit command for detailed syntax.
This parameter moves the current audit log for either an individual database or the instance to a new location for archiving and later extraction. The current timestamp will be appended to the filename. All records that are currently being written to the audit log will complete before the log is archived to ensure full records are not split apart. All records that are created while the archive is in progress will be written to the current audit log, and not the archived log, once the archive has finished.
I executed archive to archive audit logs related to sample database.
c:\temp>db2audit archive database sample
After archiving, your files will look like this:
With any subsequent command which can create audit data for sample database, db2a
5> Extract information from archived audit log in readable format:
The audit records in the archived audit log file are stored in a raw format. You must extract all the audit records from it into a text file, which can then be analyzed. You also have the option to extract audit records into delimited ASCII files, which can then be loaded into DB2 relational tables for analysis and querying.
5.a> Extracting to delimited ASCII files
To extract records from the db2audit.log file into delimited ASCII files that you can then load into DB2 relational tables, I used following command:
c:\temp>db2audit extract delasc delimiter ; from files db2a
The extracted audit records are placed in a delimited ASCII format suitable for loading into DB2 database relational tables. The output is placed in separate files, one for each category. In addition, the file auditlobs will also be created to hold any lobs that are included in the audit data. The filenames are:
Later you can create tables to load these delimited files for easy analysis. You can refer this for complete table creation process.
5.b> Extracting to text file
c:\temp>db2audit extract file out.txt from files db2a
Contents of out.txt are like this:
This clearly shows us, execution id SGTHORVE tried to login to connect to database sample using userid db2admin, which failed with -30082 error.
This is good developerworks article, which I used. Hope you enjoy your db2audit more now.
Thanks for reading!