Querying the audit trail

To query the audit trail, use SQL to select and summarize the audit data. If you follow the procedures discussed in “Setting up audit trail management”, the audit data is automatically archived to one or more tables in another database. For example, assume that the audit data resides in a table called audit_data in the audit_db database. To select audit records for tasks performed by “bob” on July 5, 1993, execute:

use audit_db
go
select * from audit_data
   where loginname = "bob"
   and eventtime like "Jul 5% 93"
go

This command requests audit records for commands performed in the pubs2 database by users with the System Security Officer role active:

select * from audit_data
   where extrainfo like "%sso_role%"
   and dbname = "pubs2"
go

This command requests audit records for all table truncations (event 64):

select * from audit_data
   where event = 64
go

To query the audit trail using the name of an audit event, use the audit_event_name function. For example, to request the the audit records for all database creation events, enter:

select * from audit_data where audit_event_name(event)
   = “Create Database”
go

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF