Last time we went over the basics of creating a SQL Audit. Now obviously once you’ve gotten your audit in place, you’re going to want to look at the output once in a while, right? Right. So that’s what we’re going to go over today.

If you’re using the default file output for your audit, you have two options for reading your audit output: the log viewer and the fn_get_audit_file function.

Log Viewer

We briefly touched on using the log viewer last time, but in case you missed that post, you can view the audit logs by right-clicking on the Audit object and selecting View Audit Logs. The nice thing about the log viewer is that it’s convenient for taking a quick look at your most recent audit records, without having to know the exact path and file name of your current audit file. On the downside, you’re limited to the most recent 1000 records, so if you’ve got a busy system generating a lot of audit records, you might miss something. And you really can’t run reports or archive records using the Log Viewer, now can you? So, if you’re going to use audit files and do some serious auditing, you’ll want a more powerful tool.

fn_get_audit_file

Fortunately, we have that tool in the fn_get_audit_file function. The great thing about this function is that it allows us to treat the audit output file like a table; so we can search, filter and order our audit records like any other data. We can insert it into a table for archival and reporting purposes, and we can join it with other audit files to find trends in our audit data. And, unlike the Log Viewer, we’re not limited in the number of records we can view.

Using fn_get_audit_file

The fn_get_audit_file function accepts 3 parameters:

• file_pattern – The first parameter is the file pattern, which specifies the path and file name of the audit file(s) to be read. You have to specify a path and a file name, though the file name can be or include a wildcard. So, for example, acceptable values would be ‘d:\myAudits\MyAudit*.sqlaudit’ or ‘\\Myserver\d$\myAudits\*’. You can also specify a specific file name, if that’s the only one you want to read.
• initial_file_name – The second parameter is the initial file name. Suppose there were multiple files in d:\myAudits that started with MyAudit*, but I didn’t want to process them all. I could use this parameter to tell SQL Server which file to start with, and it will read that file and the remaining files after it.
• audit_record_offset – This last parameter is used in conjunction with the initial file name to tell SQL Server where in that initial file to start. This comes in handy when you’ve already processed some records in that initial file, and you just want to pick up where you left off.

Examples

Let’s look at some examples using the DDLAudit audit I created last week. We’ll start with a basic query, reading in all the records in all the files we’ve accumulated so far.

 select * from fn_get_audit_file ('D:\SQL2012\Audits\DDLAudit*.sqlaudit', DEFAULT, DEFAULT) 

In my case, I only get four records returned, but that’s ok for this demo. If I scroll over to the file_name and audit_file_offset columns, I can make a note of my last audit record so far.

I’ll create and drop a table in AdventureWorks2012 to generate a couple of audit records.

 Use AdventureWorks2012
GO
CREATE TABLE myAuditTest2 (col1 int);
GO
DROP TABLE myAuditTest2;
GO 

Now, if I run that same basic query again, I’ll get the new audit records in addition to the old records I’ve already viewed. But, if I use the initial_file_name and audit_file_offset parameter to tell SQL Server where I left off last time, I’ll only get the new records.

 select * from fn_get_audit_file
   ('D:\SQL2012\Audits\DDLAudit*.sqlaudit',
    'D:\SQL2012\Audits\DDLAudit_D50CF1AD-2927-44C7-AFD0-0C31D302CA35_0_129861627977120000.sqlaudit',
    5632)

If we only wanted to see object creation records, and we wanted to know the owner of the database where the event took place, we could use the following:

 select p.name, a.database_name, a.schema_name, a.object_name, a.statement
from fn_get_audit_file
   ('D:\SQL2012\Audits\DDLAudit*.sqlaudit',
    'D:\SQL2012\Audits\DDLAudit_D50CF1AD-2927-44C7-AFD0-0C31D302CA35_0_129861627977120000.sqlaudit',
    5632) a
join sys.databases d on a.database_name = d.name
join sys.server_principals p on p.sid = d.owner_sid
where action_id = 'CR'
 

What’s next?

Now that we’ve covered the basics of creating an audit and reading its output, we can put this knowledge to use creating a solid auditing solution for our SQL Server instance. That’s what we’ll do next time.