Saturday, October 1, 2016

SQL Server Audit shows wrong data and time?

It was bit funny, could not figure out immediately what has happened, the records related to Audit are saved with wrong data and time.

SQL Server Audit is a the primary auditing tool in SQL Server and it is based on event-driven monitoring engine called Extended Events. It is used for tracking actions performed against the database and server instance, mainly for auditing.

I have written three posts on it, have a look on if you are new to SQL Server Audit;

While discussing the topic, we witnessed that SQL Server has saved audit records with wrong date and time. It took few minutes to realize that it is NOT wrong at all, records are save with UTC time standard. If you need to get the write data and time, you need to covert it to the time based on your time zone.

In my case, in order to see the right date and time for my audit records, I have to convert the UTC to +05:30 time zone like below.

 convert(datetime, SWITCHOFFSET(event_time, '+05:30')) event_time
 , object_name, user_defined_event_id, statement, user_defined_information
FROM sys.fn_get_audit_file ('c:\temp\*',default,default);

Now the question is, how to see the same if Audit is recorded with Windows Application Log or Security Log. Not sure about the way but will check and see. If you have already found a solution, please share.

No comments: