Sql Server - How to convert Audit log into a table?

Question

I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what happened to the data in the past.

So, I came to know there is a partial support of Audit feature in the Sql Server Standard Edition, and was able to log some operations, as attached.

My question is, how to make those logs stored inside a proper Sql Table so that the records could be accessed, retrieved and displayed on the web page?

Currently, the Audit Destination allowed: File, Security Log, Application Log ... which don't seem to be able to be retrieved directly from an ASP.NET API.

Is it possible to log into an SQL Table?

P.S.:

(Correct me if I am wrong). Many have said that the Standard Edition only allows Server-Level Audit, but NOT Database Level Audit. However, for some unknown reasons, if I added these 7 audit types (yup, 7 or none), I am able to capture and view the database level operations.

Answer 1

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.

Answer 2

I use the following technique to capture audit logs and store them in a table. Perhaps you can adapt it for your needs. We actually use Enterprise edition, so we're probably able to capture more than what is available in Standard.

• Configure your audit to write to a file system and limit the max size to something like 10mb. This will force new audit files to be created when that size is met. We use a shared folder because we have multiple servers writing audit information and we use ONE capture process that runs on a separate server.

• Create a stored procedure (an example follows) that will process the audit files using sys.fn_get_audit_file. Create an Agent job to run the capture stored procedure periodically (we run every minute).

• The stored procedure uses Powershell to 'move' audit files from the main storage folder to a 'staging' folder. We bypass 'locked' files that are still being written to by the audit process and will pick them up on the next capture cycle. The stored procedure reads the audit files using sys.fn_get_audit_file and inserts the information into a history table. You can also include a WHERE clause to limit the information that is selected from the audit file. We then invoke Powershell again to delete the 'staging' audit files.

Here is the stored procedure:

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

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\<SharedFolder>\SQLAuditLogs-SC\*.sqlaudit \\<SharedFolder>\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('<SharedFolder\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\<SharedFolder>\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END

Answer 3

You might look at Logbinder. They have different save options such as Windows Security Log, syslog, text file, or a custom Windows event log. https://www.logbinder.com/Products/LOGbinderSQL/