Extended AWS MS SQL RDS DB audit file search with a server timestamp-CodePudding

I am getting MS SQL RDS DB audit search with following query:

SELECT *
FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\*.sqlaudit', default, default)

Timestamp is coming in event_time column and it is in UTC, however, I need to convert it to the querying server timezone. Is it possible to extend this search with extra column with recalculated timezone?

CodePudding user response：

This has less to do with AWS or even SQL Audit and more "how do I convert UTC time to local time?". This should get you there:

SELECT *,
   [event_time_local] = SWITCHOFFSET(event_time, DATENAME(TzOffset, SYSDATETIMEOFFSET()))
FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\*.sqlaudit', default, default);

This will convert the event_time column into whatever timezone the SQL Server is in. If you need it to be a different timezone than that, you're welcome to provide a different offset to the SWITCHOFFSET() function.