Audit log reporting options? RRS feed

  • Question

  • Is there a better way to report audit log information to end user? We have requirement to send out a nice formatted alert report automatically to security auditor - wondering what option we have to read this information to report. Thanks.


    Tuesday, November 14, 2017 10:29 PM

All replies

  • This is not the SQL Audit log. This is the sql error log showing traces starting and stopping.

    To see the SQL Audit log you should check to see if auditing is enabled.

    Wednesday, November 15, 2017 12:03 AM
  • This is not the Audit Log. However, if you need to filter the error log, you may create a script to load error log to a temporary table and then filter from there..

    e.g. in your script

    1. create a temporary table

    CREATE TABLE ##ErrorLogInfo (Logdate datetime, ProcessInfo varchar(200),Msg_Text varchar(max))

    2. read and load records to the temp table

    INSERT INTO ##ErrorLogInfo EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @startdate, @enddate;

    Note: you may specify the start date and end date if you want to check the details from a specific time frame

    3. Filter the records as you wish.. e.g.

    SELECT * FROM ##ErrorLogInfo WHERE Msg_Text like '%Setting database option OFFLINE%' or
            Msg_Text like '%sql server is terminating due to%'  or
            Msg_Text like '%the log is out of space%'  or
            Msg_Text like '%error: 9002%'  or
            Msg_Text like '%severity: 13%'  or
            Msg_Text like '%severity: 17%'  or
            Msg_Text like '%severity: 18%'  or
            Msg_Text like '%severity: 19%'  or
            Msg_Text like '%severity: 20%'  or
            Msg_Text like '%severity: 21%'  or
            Msg_Text like '%severity: 22%'  or
            Msg_Text like '%severity: 23%' 

    4. Drop the temp table

    DROP TABLE ##ErrorLogInfo

    5. If you further develop this query, you can even email the output directly to your auditor. Otherwise, copy the output in to an excel sheet..

    Hope this helps..

    Wednesday, November 15, 2017 1:01 AM
  • Hi Hilary, Thanks for responding. It is SQL Audit log only, but the logging is set to Application log not File log - if you select and expand the Windows NT you will see the Application log option.  I changed it to File log, and I see exactly what you have in your window.

    So my question remains, are there any SSRS or packaged reports/alerts available to setup for auditor's view as we can't set up this SSMS Log file viewer for them.



    Wednesday, November 15, 2017 3:14 AM