SQL Server Audit – Get Date from Audit Filename

auditsql servertype conversion

SQL Server audit filenames use the following pattern:

Name_GUID_PartitionNumber_Timestamp.sqlaudit

An example filename below:

Audit-ObjectChanges_2D94C1E8-CCAD-405F-A9F2-A45FA5EDAD8F_0_132327053636850000.sqlaudit

If I query sys.dm_server_audit_status I see the status_time column has a value of 2020-04-30 08:29:22.6070000 for four audits I have. All filenames have 132327053636850000 in the timestamp part of the filename.

So I assume 132327053636850000 = 2020-04-30 08:29:22.6070000, but how can I convert the filename timestamp into datetime format for previous audit files?

Thanks

Best Answer

This looks like number of 100 nanosecond ticks since 1601-01-01 (FILETIME)

So based on that the following works (125911584000000000 is the number of these ticks between 1601 and 2000. This is subtracted first to avoid overflowing an int)

It also looks like the time returned is UTC so you will need to convert to your local time zone (which I have made an assumption about).

DECLARE @value    BIGINT = 132327053636850000,
        @datetime DATETIME2

SELECT @datetime = DATEADD(MILLISECOND, 
                           @value%10000000 / 10000, 
                             DATEADD(SECOND, 
                             ( @value - 125911584000000000 ) / 10000000, 
                             CAST('2000-01-01' AS DATETIME2)))

SELECT CONVERT(DATETIME2(3), 
                 SWITCHOFFSET(@datetime,
                 DATEPART(TZOFFSET, @datetime AT TIME ZONE 'GMT Standard Time')))

Returns

2020-04-30 08:29:23.685
Related Question