2

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

4

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
| improve this answer | |
  • 1
    Perfect. I've checked with previous files and really happy with this. Thank you. – boomcubist May 5 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.