Long Scripts

How To Query The SQL Audit Log

Here’s a quick script to query the SQL Audit log. Remember; you could always do a SELECT * FROM sys.fn_get_audit_file, but this could be a reeeaaallly looong time before you get the results you want cause it’s look across a file on the disk.

For a more isolated view of what you are looking for; try this little piece of logic.

use master;
set nocount on
select
'run at' = convert(char, event_time, 9) , 'user' = database_principal_name
, 'database' = database_name
, 'object' = object_name
, 'statement' = statement
from
sys.fn_get_audit_file
(
'\\MyShare\MyFolder\My_Audit_file_770000.sqlaudit'
, default
, default
)
where
database_name = 'MyDatabase'
and event_time between '2014-07-10 15:00' and '2014-07-10 16:00' order by
event_time desc

hope this is useful

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s