Viewing archived audit records

Learn how to view audit records.

Learn how to view audit records from audit log from your Cloud Object Storage (COS) bucket.

If applicable, you can preview the audit logs through the user interface by clicking the preview icon on the right on each file.

To view a larger subset of records, you will need to perform further processing using a Db2 server.

If the audit logs are in binary format, you will need to download the audit logs from your COS bucket and extract the audit logs as a report using the following command:

db2audit extract delasc from files <full_path_of_downloaded_audit_log>

For example,

db2audit extract delasc delimiter : from files /tmp/db2audit.db.BLUDB.log.0.20230620081610

You can also extract it as a delimited file if you want to load the audit records to a Db2 database table.

db2audit extract file <report_file_name> from path <path_where_audit_logs_downloaded> files <audit_log_names>

For example,

db2audit extract file db2audit.db.BLUDB.log.0.20230620081610.txt from path /tmp/ files db2audit.db.BLUDB.log.0.20230620081610

If the audit logs are in the delimited format, you can directly import the records into a database table. You will need to filter out which DEL files are needed based on the timestamp. The downloaded DEL files can be directly imported into a Db2 table using the IMPORT command. You will be responsible for deleting this data from the database to prevent out of disk space issues.

You can load the DEL files directly from COS, as exemplified in the following code sample. See Db2 CREATE EXTERNAL TABLE for further options. This external table method is suitable for viewing audit records without LOB data. For the EXECUTE or CONTEXT categories, a file named auditlobs is generated when extracting audit records to DEL format. However, external tables cannot read this LOB data from COS.

CREATE EXTERNAL TABLE ARCHIVED_AUDIT( TIMESTAMP CHAR(26 OCTETS),
					CATEGORY CHAR(8 OCTETS),
					EVENT VARCHAR(32 OCTETS),
					CORRELATOR CHAR(8 OCTETS),
					STATUS CHAR(8 OCTETS),
					USERID VARCHAR(1024 OCTETS),
					AUTHID VARCHAR(128 OCTETS),
					DATABASE CHAR(8 OCTETS),
					NODENUM CHAR(8 OCTETS),
					COORDNUM CHAR(8 OCTETS),
					APPID VARCHAR(255 OCTETS),
					APPNAME VARCHAR(1024 OCTETS),
					PKGSCHEMA VARCHAR(128 OCTETS),
					PKGNAME VARCHAR(128 OCTETS),
					PKGSECNUM CHAR(8 OCTETS),
					PKGVER VARCHAR(64 OCTETS),
					LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
					GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
					CLNTUSERID VARCHAR(255 OCTETS),
					CLNTWRKSTNAME VARCHAR(255 OCTETS),
					CLNTAPPNAME VARCHAR(255 OCTETS),
					CLNTACCSTRING VARCHAR(255 OCTETS),
					TRSTCTXNAME VARCHAR(255 OCTETS),
					CONTRSTTYPE CHAR(1 OCTETS),
					ROLEINHERITED VARCHAR(128 OCTETS),
					POLNAME VARCHAR(128 OCTETS),
					POLASSOCOBJTYPE CHAR(1 OCTETS),
					POLASSOCSUBOBJTYPE CHAR(1 OCTETS),
					POLASSOCNAME VARCHAR(128 OCTETS),
					POLASSOCOBJSCHEMA VARCHAR(128 OCTETS),
					AUDITSTATUS CHAR(1 OCTETS),
					CHECKINGSTATUS CHAR(1 OCTETS),
					CONTEXTSTATUS CHAR(1 OCTETS),
					EXECUTESTATUS CHAR(1 OCTETS),
					EXECUTEDATA CHAR(1 OCTETS),
					OBJMAINTSTATUS CHAR(1 OCTETS),
					SECMAINTSTATUS CHAR(1 OCTETS),
					SYSADMINSTATUS CHAR(1 OCTETS),
					VALIDATESTATUS CHAR(1 OCTETS),
					ERRORTYPE CHAR(8 OCTETS),
					DATAPATH VARCHAR(1024 OCTETS),
					ARCHIVEPATH VARCHAR(1024 OCTETS),
					ORIGUSERID VARCHAR(1024 OCTETS),
					INSTNAME VARCHAR(128 OCTETS),
					HOSTNAME VARCHAR(255 OCTETS),
					TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<audit.del>'
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

CREATE EXTERNAL TABLE ARCHIVED_CHECKING( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						STATUS CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						OBJSCHEMA VARCHAR(128 OCTETS),
						OBJNAME VARCHAR(128 OCTETS),
						OBJTYPE VARCHAR(32 OCTETS),
						ACCESSAPP CHAR(34 OCTETS),
						ACCESSATT CHAR(34 OCTETS),
						PKGVER VARCHAR(64 OCTETS),
						CHKAUTHID VARCHAR(128 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
						ACCESSCONTROLMGR CHAR(8 OCTETS),
                        TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<checking.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

CREATE EXTERNAL TABLE ARCHIVED_OBJMAINT ( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						STATUS CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						OBJSCHEMA VARCHAR(128 OCTETS),
						OBJNAME VARCHAR(128 OCTETS),
						OBJTYPE VARCHAR(32 OCTETS),
						PACKVER VARCHAR(64 OCTETS),
						SECPOLNAME VARCHAR(128 OCTETS),
						ALTERACTION VARCHAR(32 OCTETS),
						PROTCOLNAME VARCHAR(128 OCTETS),
						COLSECLABEL VARCHAR(128 OCTETS),
						SECCOLNAME VARCHAR(128 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						MODULENAME VARCHAR(128 OCTETS),
						ASSOCOBJNAME VARCHAR(128 OCTETS),
						ASSOCOBJSCHEMA VARCHAR(128 OCTETS),
						ASSOCOBJTYPE VARCHAR(32 OCTETS),
						ASSOCSUBOBJNAME VARCHAR(128 OCTETS),
						ASSOCSUBOBJTYPE VARCHAR(32 OCTETS),
						SECURED VARCHAR(32 OCTETS),
						STATE VARCHAR(32 OCTETS),
						ACCESSCONTROL VARCHAR(32 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
                        TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<objmaint.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));


CREATE EXTERNAL TABLE ARCHIVED_SECMAINT ( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						STATUS CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						OBJSCHEMA VARCHAR(128 OCTETS),
						OBJNAME VARCHAR(128 OCTETS),
						OBJTYPE VARCHAR(32 OCTETS),
						GRANTOR VARCHAR(128 OCTETS),
						GRANTEE VARCHAR(128 OCTETS),
						GRANTEETYPE VARCHAR(32 OCTETS),
						PRIVAUTH CHAR(34 OCTETS),
						PKGVER VARCHAR(64 OCTETS),
						ACCESSTYPE VARCHAR(32 OCTETS),
						ASSUMEAUTHID VARCHAR(128 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						GRANTORTYPE VARCHAR(32 OCTETS),
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXUSER VARCHAR(128 OCTETS),
						TRSTCTXUSERAUTH CHAR(8 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						ALTERACTION VARCHAR(32 OCTETS),
						ASSOCOBJNAME VARCHAR(128 OCTETS),
						ASSOCOBJSCHEMA VARCHAR(128 OCTETS),
						ASSOCOBJTYPE VARCHAR(32 OCTETS),
						ASSOCSUBOBJNAME VARCHAR(128 OCTETS),
						ASSOCSUBOBJTYPE VARCHAR(32 OCTETS),
						SECURED VARCHAR(32 OCTETS),
						STATE VARCHAR(32 OCTETS),
						ACCESSCONTROL VARCHAR(32 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
                        TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<secmaint.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

CREATE EXTERNAL TABLE ARCHIVED_SYSADMIN ( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						STATUS CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						PKGVER VARCHAR(64 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						EVENTDETAILS VARCHAR(2048 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
                        TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<sysadmin.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

CREATE EXTERNAL TABLE ARCHIVED_VALIDATE ( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						STATUS CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						EXECID VARCHAR(1024 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						AUTHTYPE VARCHAR(32 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						PKGVER VARCHAR(64 OCTETS),
						PLUGINNAME VARCHAR(32 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
                        TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<validate.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

CREATE EXTERNAL TABLE ARCHIVED_CONTEXT ( TIMESTAMP CHAR(26 OCTETS),
						CATEGORY CHAR(8 OCTETS),
						EVENT VARCHAR(32 OCTETS),
						CORRELATOR CHAR(8 OCTETS),
						DATABASE CHAR(8 OCTETS),
						USERID VARCHAR(1024 OCTETS),
						AUTHID VARCHAR(128 OCTETS),
						NODENUM CHAR(8 OCTETS),
						COORDNUM CHAR(8 OCTETS),
						APPID VARCHAR(255 OCTETS),
						APPNAME VARCHAR(1024 OCTETS),
						PKGSCHEMA VARCHAR(128 OCTETS),
						PKGNAME VARCHAR(128 OCTETS),
						PKGSECNUM CHAR(8 OCTETS),
						STMTTEXT CLOB(32 OCTETS),
						PKGVER VARCHAR(64 OCTETS),
						LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
						GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
						CLNTUSERID VARCHAR(255 OCTETS),
						CLNTWRKSTNAME VARCHAR(255 OCTETS),
						CLNTAPPNAME VARCHAR(255 OCTETS),
						CLNTACCSTRING VARCHAR(255 OCTETS),
						TRSTCTXNAME VARCHAR(255 OCTETS),
						CONTRSTTYPE CHAR(1 OCTETS),
						ROLEINHERITED VARCHAR(128 OCTETS),
						ORIGUSERID VARCHAR(1024 OCTETS),
						INSTNAME VARCHAR(128 OCTETS),
						HOSTNAME VARCHAR(255 OCTETS),
                       TENANTNAME VARCHAR(128 OCTETS))
	using(dataobject '<context.del>' 
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));

		CREATE EXTERNAL TABLE ARCHIVED_EXECUTE(TIMESTAMP CHAR(26 OCTETS),
					  	CATEGORY CHAR(8 OCTETS),
					  	EVENT VARCHAR(32 OCTETS),
					  	CORRELATOR CHAR(8 OCTETS),
					  	STATUS CHAR(8 OCTETS),
					  	DATABASE CHAR(8 OCTETS),
					  	USERID VARCHAR(1024 OCTETS),
					  	AUTHID VARCHAR(128 OCTETS),
					  	SESSNAUTHID VARCHAR(128 OCTETS),
					  	NODENUM CHAR(8 OCTETS),
					  	COORDNUM CHAR(8 OCTETS),
					  	APPID VARCHAR(255 OCTETS),
					  	APPNAME VARCHAR(1024 OCTETS),
					  	CLNTUSERID VARCHAR(255 OCTETS),
					  	CLNTWRKSTNAME VARCHAR(255 OCTETS),
					  	CLNTAPPNAME VARCHAR(255 OCTETS),
					  	CLNTACCSTRING VARCHAR(255 OCTETS),
					  	TRSTCTXNAME VARCHAR(255 OCTETS),
					  	CONTRSTTYPE CHAR(1 OCTETS),
					  	ROLEINHERITED VARCHAR(128 OCTETS),
					  	PKGSCHEMA VARCHAR(128 OCTETS),
					  	PKGNAME VARCHAR(128 OCTETS),
					  	PKGSECNUM CHAR(8 OCTETS),
					  	PKGVER VARCHAR(64 OCTETS),
					  	LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA,
					  	GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA,
					  	UOWID CHAR(8 OCTETS),
					  	ACTIVITYID CHAR(8 OCTETS),
					  	STMTINVOCID CHAR(8 OCTETS),
					  	STMTNESTLVL CHAR(8 OCTETS),
					  	ACTIVITYTYPE VARCHAR(32 OCTETS),
					  	STMTTEXT CLOB(32 OCTETS),
					  	STMTISOLATIONLVL CHAR(8 OCTETS),
					  	COMPENVDESC BLOB(8K),
					  	ROWSMODIFIED CHAR(8 OCTETS),
					  	ROWSRETURNED CHAR(8 OCTETS),
					  	SAVEPOINTID CHAR(8 OCTETS),
					  	STMTVALINDEX CHAR(8 OCTETS),
					  	STMTVALTYPE CHAR(16 OCTETS),
					  	STMTVALDATA CLOB(128K OCTETS),
					  	STMTVALEXTENDEDINDICATOR CHAR(8 OCTETS),
					  	LOCAL_START_TIME CHAR(26 OCTETS),
					  	ORIGUSERID VARCHAR(1024 OCTETS),
					  	INSTNAME VARCHAR(128 OCTETS),
					  	HOSTNAME VARCHAR(255 OCTETS),
                       TENANTNAME VARCHAR(128 OCTETS)) 
	using(dataobject '<execute.del>'
		IGNOREZERO TRUE 
		TRUNCSTRING TRUE 
		CCSID 1208 
		DELIMITER ',' 
		TIMEDELIM ' ' 
		DATEDELIM ' ' 
		CTRLCHARS ON 
		FILLRECORD ON 
		MAXERRORS 1000 
s3('<endpoint>', 
		'<authKey1>',
		'<authKey2>', 
		'<bucket>'));