Auditing doesn’t have to be scary. SQL Server Audit 102 – Reading Audit Output is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.
SQL Server Audit 102 – Reading Audit Output
Originally published on ColleenMorrow.com.
In SQL Server Audit 101 – Creating Basic Audit, 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.
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.
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.
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.
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'
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 in SQL Server Audit 201 – Creating an Audit Solution.