SQL Server Audit 102 Reading Audit Output

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

SQL Server Audit 102 – Reading Audit OutputOriginally 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.

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.

Get-audit-offset

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)

Use-audit-offset

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 in SQL Server Audit 201 – Creating an Audit Solution.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a leading Microsoft Gold Partner for organizations who rely on Microsoft’s Data Platforms, and its mission is to enable every leader to unlock data’s full potential.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *