SQL Server – How to Load Data from SQL Server Audit File to a Table

auditsql serversql-server-2008-r2

I have enabled Auditing in SQL Server 2008 R2 to monitor DML and DDL operations.

Is there any way to load SQLAudit files to the database? Or any other method to automatically load audit details directly to a database?

Best Answer

The following process has worked well for us.

We write our audit files to a file share. I have a Sql Server instance dedicated to processing these audit files. On this SQLAudit instance, I have a job that runs every minute and executes a stored procedure.

The stored procedure:

Uses Powershell to move any audit files (that are not currently being written to) to a staging fileshare. The powershell command uses the -ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to the staging fileshare. Eventually, the 'locked' files will become available to be copied on a future interval.
Now that I have available SQLAudit files to read from the staging fileshare, we're able to use sys.fn_get_audit_file to process all SQLAudit files from the staging fileshare by using wildcards.
After consuming the files from the staging fileshare, I use Powershell to delete the files from the staging fileshare.

Here's the stored procedure.

CREATE PROCEDURE [dbo].[SqlAuditCaptureAuditLogs]
AS
BEGIN
    SET XACT_ABORT ON

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\*.sqlaudit \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"'
        ,no_output

    INSERT INTO SQLAUDIT.[dbo].[SQLAUDIT_HISTORY] (
        event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
        )
    SELECT event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
    FROM sys.fn_get_audit_file('\\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)
    WHERE server_principal_name NOT IN (
            ,'mfoperational'
            ,'mfreader'
            ,'aperioapplication'
            ,'aperioreader'
            ,'aperiosync'
            ,'aperiobuild'
            ,'DataFix'
            ,'mapinfo'
            ,'GeoSpatialUpdater'
            ,'sa'
            ,'ussql1'
            ,'usweb'
            ,'uswebs'
            ,'vmview'
            ,'dba-admin'
            )

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END

Related Solutions

SQL Server Performance – Extended Events vs SQL Audit

I created a simple test rig to trial SQL Server Audit against triggers, and potentially other options. In my tests of inserting 1 million rows into a table I got 52, 67 and 159 seconds for baseline, SQL Audit and my trigger respectively:

Now this isn't particularly scientific but does potentially offer you one way of comparing approaches. Have a look through the script, see if it can be of use to you:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'testAuditDb' )
ALTER DATABASE testAuditDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'testAuditDb' )
DROP DATABASE testAuditDb
GO


CREATE DATABASE testAuditDb
ON PRIMARY
( NAME = N'testAuditDb', FILENAME = N's:\temp\testAuditDb.mdf', SIZE = 1GB, MAXSIZE = UNLIMITED, FILEGROWTH = 128MB )
LOG ON 
( NAME = N'testAuditDb_log', FILENAME = N's:\temp\testAuditDb_log.ldf', SIZE = 100MB, MAXSIZE = 2048GB, FILEGROWTH = 128MB )
GO

ALTER DATABASE testAuditDb SET RECOVERY SIMPLE
GO



------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

USE testAuditDb
GO

CREATE SCHEMA auditSchema

-- Create a table
CREATE TABLE auditSchema.auditTable ( 
    rowId INT IDENTITY PRIMARY KEY, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    dateAdded DATETIME DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION 
)
GO


-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Test 01 - Baseline START
-- Normal timing; no triggers or audits
------------------------------------------------------------------------------------------------


-- Add a million rows to the table and time it.
DECLARE @i INT = 0, @startTime DATETIME2, @endTime DATETIME2

SET @startTime = SYSDATETIME()

WHILE @i < 1000000
BEGIN

    INSERT INTO auditSchema.auditTable DEFAULT VALUES

    SET @i += 1
END

SET @endTime = SYSDATETIME()

SELECT DATEDIFF( second, @startTime, @endTime ) AS baseline
GO


-- Cleanup
TRUNCATE TABLE auditSchema.auditTable 
GO

-- Test 01 - Baseline END
------------------------------------------------------------------------------------------------





------------------------------------------------------------------------------------------------
-- Test 02 - SQL Audit START
-- Try SQL Audit
------------------------------------------------------------------------------------------------

-- Create server audit in master database
USE master
GO

------------------------------------------------------------------------------------------------------------------------
-- The server audit is created with a WHERE clause that limits the server audit to only the auditTable table.
------------------------------------------------------------------------------------------------------------------------
CREATE SERVER AUDIT auditTableAccess TO FILE ( FILEPATH = 'S:\SQLAudit\' ) WHERE object_name = 'auditTable';
GO
ALTER SERVER AUDIT auditTableAccess WITH ( STATE = ON );
GO

-- Create the database audit specification in the testAuditDb database 
USE testAuditDb;
GO

CREATE DATABASE AUDIT SPECIFICATION [dbAudit1]
FOR SERVER AUDIT auditTableAccess
ADD ( 
    SELECT, INSERT, UPDATE ON SCHEMA::[auditSchema]
    BY [public]
    ) WITH ( STATE = ON );
GO


-- Add a million rows to the table and time it.
DECLARE @i INT = 0, @startTime DATETIME2, @endTime DATETIME2

SET @startTime = SYSDATETIME()

WHILE @i < 1000000
BEGIN

    INSERT INTO auditSchema.auditTable DEFAULT VALUES

    SET @i += 1
END

SET @endTime = SYSDATETIME()

SELECT DATEDIFF( second, @startTime, @endTime ) AS sqlAudit
GO


-- Cleanup
TRUNCATE TABLE auditSchema.auditTable
GO
ALTER DATABASE AUDIT SPECIFICATION [dbAudit1] WITH ( STATE = Off );
DROP DATABASE AUDIT SPECIFICATION [dbAudit1]
GO

USE master
ALTER SERVER AUDIT auditTableAccess WITH ( STATE = OFF );

DROP SERVER AUDIT auditTableAccess
GO



/*
-- Inspect the audit output
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT *
INTO #tmp
FROM fn_get_audit_file ( 'S:\SQLAudit\auditTableAccess_*.sqlaudit', DEFAULT, DEFAULT );
GO


SELECT statement, MIN(event_time), MAX(event_time), COUNT(*) AS records
FROM #tmp
GROUP BY statement
GO
*/

-- Test 02 - SQL Audit END
------------------------------------------------------------------------------------------------




------------------------------------------------------------------------------------------------
-- Test 03 - Triggers START
-- Trial INSERT/UPDATE trigger with log table
------------------------------------------------------------------------------------------------
USE testAuditDb
GO

CREATE TABLE dbo.auditLog
    (
    auditLogLog     INT IDENTITY PRIMARY KEY,
    schemaName      SYSNAME NOT NULL,
    tableName       SYSNAME NOT NULL,
    dateAdded       DATETIME NOT NULL DEFAULT GETDATE(),
    addedBy         SYSNAME NOT NULL DEFAULT SUSER_NAME(),
    auditXML        XML
    )
GO


-- Generic audit trigger
CREATE TRIGGER trg_dbo__triggerTest ON auditSchema.auditTable
FOR INSERT, UPDATE, DELETE

AS

BEGIN

    IF @@rowcount = 0 RETURN

    SET NOCOUNT ON

    DECLARE @action VARCHAR(10)

    IF EXISTS ( SELECT * FROM inserted )
    AND EXISTS ( SELECT * FROM deleted )
        SET @action = 'UPDATE'
    ELSE IF EXISTS ( SELECT * FROM inserted )
        SET @action = 'INSERT'
    ELSE IF EXISTS ( SELECT * FROM deleted )
        SET @action = 'DELETE'

    INSERT INTO dbo.auditLog ( schemaName, tableName, auditXML )
    SELECT OBJECT_SCHEMA_NAME( parent_id ) schemaName, OBJECT_NAME( parent_id ) tableName,
        (
        SELECT
            @action "@action",
            ( SELECT 'inserted' source, * FROM inserted FOR XML RAW, TYPE ),
            ( SELECT 'deleted' source, * FROM deleted FOR XML RAW, TYPE )
        FOR XML PATH('mergeOutput'), TYPE
        ) x
    FROM sys.triggers
    WHERE OBJECT_ID = @@procid
      AND ( EXISTS ( SELECT * FROM inserted )
         OR EXISTS ( SELECT * FROM deleted )
          )

END
GO


-- Add a million rows to the table and time it.
DECLARE @i INT = 0, @startTime DATETIME2, @endTime DATETIME2

SET @startTime = SYSDATETIME()

WHILE @i < 1000000
BEGIN

    INSERT INTO auditSchema.auditTable DEFAULT VALUES

    SET @i += 1
END

SET @endTime = SYSDATETIME()

SELECT DATEDIFF( second, @startTime, @endTime ) AS triggers
GO

-- Cleanup
TRUNCATE TABLE auditSchema.auditTable
DROP TABLE dbo.auditLog
DROP TRIGGER auditSchema.trg_dbo__triggerTest
GO

-- Test 03 - Triggers END
------------------------------------------------------------------------------------------------

Whilst the trigger option didn't do very well here, my trigger code could be simplified depending on what you want to capture and it does allow you access to the old and new values in a fairly usable format which SQL Audit does not. I have used this technique for a lower-activity config table and it works quite well. Depending on what you want to capture you could also consider Change Data Capture.

Let me know how you get on with your trials. Good luck.

SQL Server – How to Convert Audit Log into a Table

Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.

If you want to trace DML operations, another possible hook is Extended Events. You won't be able to use the auditing package, but you could use lock acquired events to capture read and write operations.

I blogged about it here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Other options are:

Triggers: this is easy to set up, even automatically by generating the trigger code. Downside is the increased write overhead.
App-side logging: If you have control over the application, you can inject the code needed to log accesses to the tables.

Best Answer

Related Solutions

SQL Server Performance – Extended Events vs SQL Audit

SQL Server – How to Convert Audit Log into a Table

Related Question