SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views

6. Viewing Audited Events

After you enable your audit objects and audit specifications, SQL Server takes it from there. Audits for each monitored event declared in your audit specifications are recorded automatically to the destinations you’ve specified in your audit objects. After accumulating several audits, you’ll want to view them, of course.

Audits recorded to the event log can be examined using the Event Viewer (available from Administrative Tools in Control Panel). For example, Figure 1 shows the properties of an event recorded by a database audit for a DELETE statement against the TestTable table displayed using the Event Viewer.

Figure 1. Displaying an audit recorded to the Application event log using Event Viewer.

Audits recorded to the file system are not stored in plain text files that can simply be viewed in Notepad. Instead, they are binary files that you can view in one of two ways. One way is from inside SQL Server Management Studio. Right-click the desired audit object beneath the Security node at the server instance level (not the Security node at the database level) in the Object Explorer, and then choose View Audit Logs. This opens the Log File Viewer window, as shown in Figure 2.

Each audit entry contains a wealth of detailed information about the event that was captured and recorded. This includes date and time stamp, server instance, action, object type, success or failure, permissions, principal name and ID (that is, the user that performed the audited action), database name, schema name, object name, the actual statement that was executed (or attempted), and more.

Figure 2. Displaying audits recorded to the file system using the Log File Viewer in SSMS.

Alternatively, you can use the table-valued function (TVF) named sys.fn_get_audit_file. This function accepts a parameter that points to one or more audit files (using wildcard pattern matching). Two additional parameters allow you to specify the initial file to process and a known offset location to start reading audit records from. (Both of these parameters are optional but must still be specified using the keyword default.) The function then reads the binary data from the file(s) and formats the audit entries into an ordinary table that gets returned back, as Example 9 demonstrates.

Example 9. Querying audit files.

SELECT
  event_time, database_name, object_name, statement
 FROM
  sys.fn_get_audit_file('C:\Demo\SqlAudits\*.sqlaudit', default, default)

Here are some abbreviated results from this query:

event_time              database_name object_name statement
----------------------- ------------- ----------- ----------------------------------------
  :
2012-01-05 19:33:19.381 MyDB                      CREATE TABLE TestTable(TestId int PRIM..
.
2012-01-05 19:33:45.789 MyDB          TestTable   INSERT INTO TestTable VALUES(1, 'Test'..
.
2012-01-05 19:33:45.789 MyDB          TestTable   SELECT * FROM TestTable
2012-01-05 19:33:45.789 MyDB          TestTable   DELETE FROM TestTable WHERE TestId=1
  :

You can easily filter and sort this data using WHERE and ORDER BY, as well as INSERT the data elsewhere. The sys.fn_get_audit_file function represents an advantage that auditing to the file system has over auditing to the Windows event log, as there is no equivalent function provided for querying and manipulating audits in the event logs.

7. Querying Audit Catalog Views

SQL Server provides a number of catalog views that you can query for information about the audits and audit specifications running on any server instance. These audit catalog views are listed in Table 1, accompanied by a brief description of each.

Table 1. Audit catalog views.

Catalog View Name	Description
sys.server_file_audits	Returns all of the audit objects that are recorded to the file system.
sys.server_audit_specifications	Returns all of the server-level audit specifications.
sys.server_audit_specification_details	Returns detailed, monitored event information for all of the server-level audit specifications.
sys.database_audit_specifications	Returns all of the database-level audit specifications.
sys.database_audit_specification_details	Returns detailed, monitored event information for all of the database-level audit specifications.
sys.dm_server_audit_status	Returns the status of each audit object.
sys.dm_audit_actions	Returns every audit action that can be reported on and every audit action group that can be configured.
sys.dm_audit_class_type_map	Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.