So far in the SQL Server Audit series we've looked at the different components that make up the auditing feature as well as how to create the audits with specifications specific to databases as well as server level.

Here is the links to the posts covering the topics:

SQL Server Auditing: Introduction
SQL Server Auditing: Creating a Server Specification
SQL Server Auditing: Creating a Database Specification

Now that we have set up our audits covering failed logins on instance level and deletes on database level, let's take a look at how we can see what audits are on a server as well as some views to make your life easier with reading the audit files.

DMV's and Views

First off, let's see what views are available at our disposal:

This returns the following for us:

DMV's

So what does the audit DMV's do? Let's take a look:

sys.dm_audit_actions

This DMV contains friendlier names, as well as all the actions as well as which action groups they belong to across database and server level specifications.

sys.dm_audit_class_type_map

This contains all the audit classes and action groups available.

sys.dm_server_audit_status

This takes a specific look at the audits defined on the server and returns info such as what audits are enabled, statuses for the audits, event times, etc.

Sys.dm_audit_class_type_map and sys.dm_audit_actions are especially useful to join with the other views to get a more easily readable report on the audits, or to join it with the fn_get_audit_file function when reading audit files.

Server Views

The server views are split up into two "sections", one for the audits, and the second for the server specification details.

Audits:
sys.server_audits
sys.server_file_audits

These views contain details about the server level audits defined on the server, This returns information such as audit names, file names and paths, audit id's, statuses, etc.

Specification:
sys.server_audit_specification_details
sys.server_audit_specifications

These views are very helpful when you have multiple audits on a server, and matching the various defined audit actions as well as their groups to audits.

Database Views

The database views available are almost identical to the server level ones, except the fact that it's specific to databases, and that there are no "audit" views; only for database specifications. Another thing to note is, that these views won't return information across databases on an instance, this needs to be executed per database.

sys.database_audit_specification_details
sys.database_audit_specifications

The views return the specification names, enabled audit actions, audit results, etc.

Now that we've got the views covered, a question still lingers... "How do we manage audits across multiple servers?" Well the answer is quite simple, we make use of another new feature introduced in SQL Server 2008 which is Policy Based Management, otherwise known as PBM.

So PBM is a subject on it's own, so I will not go in depth into this topic.

A key resource required for monitoring your environment is the Enterprise Policy Framework.

Below is a test policy I created for the post and will monitor the audits for the following:

1. Maximum File Size
2. On Failure Action
3. Reserve Disk Space
4. Maximum Rollover Files
5. Maximum File Size Unit ( MB, GB or TB )

This is just a basic policy using the "audit" facet. There are additional facets for Database Audit Specifications as well as Server Audit Specifications.

Additionally if you decide to use the Enterprise Policy Framework, it should be noted that you need an additional condition to be set up on the policy that will only evaluate the policy against SQL Server 2008+.

Last Thoughts

These are the views we can use to discover what audits are running across an instance or databases, which actions we are auditing for and a brief policy we can use when we have to manage the audit files specifically to ensure we have a set standard.