Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate the Import of SQL Server Audit Files into SQL Server Using PowerShell


By:   |   Read Comments (3)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

When security and auditing are crucial for your business, the IT security team would be very optimistic that you have configured the SQL Server Auditing option to monitor users actions on the production databases.  The SQL Audit files are then delivered automatically to the IT security team, but when they open these SQLAUDIT files, they find only rubbish data.  This is normal, as you need to SELECT using the fn_get_audit_file function to read these logs.

Mmm… There are numerous large files to search through for a specific action, so how could we make this easier?

Solution

If you try to open a SQLAUDIT file, you will find meaningless data as below:

If you try to open the SQLAUDIT file, you will find meaningless data

When you try to select the data using the fn_get_audit_file function using SQL Server Management Studio, the result will be like below which is much better.  The problem is that this could be time consuming to look at each file individually.

SELECT * FROM fn_get_audit_file('FileName_WithFullPath', default, default) 

Try to select the data from the fn_get_audit_file function using the SQL Management Studio.

Creating a SQL Server Audit Repository

Having a separate SQL Server, database and table to restore the audit files will make these audit logs readable for the IT security department.  In our case, a large number of production SQL Servers will be audited. We therefore created a separate SQL Server, a separate database for each audited server and the below table in each database.

CREATE TABLE [dbo].[Audit_rows](
 [event_time] [datetime2](7) NULL,
 [sequence_number] [int] NULL,
 [action_id] [varchar](4) NULL,
 [succeeded] [bit] NOT NULL,
 [permission_bitmask] [bigint] NOT NULL,
 [is_column_permission] [bit] NOT NULL,
 [session_id] [smallint] NOT NULL,
 [server_principal_id] [int] NULL,
 [database_principal_id] [int] NULL,
 [target_server_principal_id] [int] NULL,
 [target_database_principal_id] [int] NULL,
 [object_id] [bigint] NULL,
 [class_type] [varchar](10) NULL,
 [session_server_principal_name] [nvarchar](100) NULL,
 [server_principal_name] [nvarchar](100) NULL,
 [server_principal_sid] [nvarchar](100) NULL,
 [database_principal_name] [nvarchar](100) NULL,
 [target_server_principal_name] [nvarchar](100) NULL,
 [target_server_principal_sid] [nvarchar](100) NULL,
 [target_database_principal_name] [nvarchar](100) NULL,
 [server_instance_name] [nvarchar](100) NULL,
 [database_name] [nvarchar](100) NULL,
 [schema_name] [nvarchar](100) NULL,
 [object_name] [nvarchar](100) NULL,
 [statement] [nvarchar](max) NULL,
 [additional_information] [nvarchar](500) NULL,
 [file_name] [nvarchar](500) NULL,
 [audit_file_offset] [bigint] NULL,
 [user_defined_event_id] [int] NULL,
 [user_defined_information] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now we need an automated way to restore these audit files to that table, rather than running the below query on each file every time we need to restore the data, such as below:

INSERT INTO db_name.dbo.Audit_rows
SELECT * FROM fn_get_audit_file('FileName_WithFullPath', default, default) 

Automating Data Loading of SQL Server Audit Files

Here we preferred using Windows PowerShell to write a function that loops through all SQLAUDIT files in a specific folder, loading the data and moving the file to another folder and optionally sending a success email. This function is written in a readable way with full detailed logs and comments.  You can download a copy of the complete PowerShell script here.

A full description for the function’s parameters, examples and functionality can be viewed by running the get-help PowerShell command as follows:

get-help .\Invoke-CorpSQLAuditRestore.ps1 –full

Calling the Invoke-CorpSQLAuditRestore function in PowerShell is easy, you need to specify the parameters as in the two examples below (included in the function's help):

Process all audit files inside folder "C:\Source"

.\Invoke-CorpSQLAuditRestore.ps1 -ScriptFilesPath .\ -SourceDir C:\Source\ -DestDir C:\Dest\ -SQL_srv_name SRV1 -SQL_db_name db1

Process all audit files inside folder "C:\Source" and send an email

 .\Invoke-CorpSQLAuditRestore.ps1 -ScriptFilesPath .\ -SourceDir C:\Source\ -DestDir C:\Dest\ -SQL_srv_name SRV1 -SQL_db_name db1 -SendMail:$true -MailFrom noreply@contoso.com  -MailTo me@contoso.com  -MailServer smtp.contoso.com 

Example Run

Let’s try to apply the first example from the above on a folder with 4 SQLAUDIT files, a progress bar should appear showing the progress percentage along with the procedure description that looks like the below:

Apply the first example from the above on a folder with 4 SQLAUDIT files.

And the final result is like below:

This PowerShell script could be run upon demand or scheduled via windows scheduled task

This PowerShell script could be run on demand or scheduled via a Windows Scheduled Task. Last but not least, make sure that the user that runs this script has access to the database server and to the source and destination folders.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 08, 2017 - 3:17:26 PM - Scott Back To Top

The powershell and all works great!

 

The create table statement needs some updating:

[server_principal_sid] [nvarchar](100) NULL,

should be

[server_principal_sid] [varbinary] (max) NULL,

The following columns should be added:

 ,[audit_schema_version] [int]  NULL

 ,[sequence_group_id] [varbinary] (max)  NULL

 ,[transaction_id] [bigint] NULL

 


Wednesday, October 21, 2015 - 8:35:08 AM - Ahmad Yaseen Back To Top

Thanks Tyler for your comment.

Please note that any file that is still being written will be opened by the SQL Engine and the script will not be able to read it.

Best Regards,

Ahmad

 

 


Tuesday, October 20, 2015 - 5:13:40 PM - Tyler Back To Top

What does the script do if the Audit File is still being written too?


Learn more about SQL Server tools