SQL Audit logs shows wrong time on the Events

I have implemented SQL audit for  SQL Server 2012 one one cluster instance.

When i view audits logs, wondering why does it display wrong time.i goggled and found this is a known bug.

SQL server audits are logged with UTC times.

Workaround for this issues is to use the below queries to get the correct result.Auditlog_incorrect time stamp

SELECT DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time )
as corrected_time,
action_id ,
session_server_principal_name,
server_instance_name ,
database_name ,
schema_name ,
object_name ,
statement ,
file_name FROM sys.fn_get_audit_file( ‘F:\Audit\*.sqlaudit’ , DEFAULT , DEFAULT)

Additional info :

https://www.sqlskills.com/blogs/jonathan/incorrect-timestamp-on-events-in-extended-events/

https://connect.microsoft.com/SQLServer/feedback/details/349923/audit-logs-shows-wrong-time-on-the-events-two-hours-in-the-past

Advertisements

One thought on “SQL Audit logs shows wrong time on the Events

  1. Thank you, much appreciated

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