none
sql server Audit incorrect Event time RRS feed

  • Question

  • Hi,

    I have a problem with Event time in my Audit.

    As it is shown below the time of my server is different from the time of the event:

    Wednesday, September 10, 2014 7:01 AM

Answers

  • Thanks,

    Actually I did not get it.But I have this query to convert the result

    select [statement], 
         CONVERT(datetime, 
            SWITCHOFFSET(CONVERT(datetimeoffset, event_time), 
                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS
          event_time from (
    SELECT event_time,[statement] FROM sys.fn_get_audit_file 
    ('Filepath\AuditFile.sqlaudit'
    ,default,default)
    ) x 

    Thursday, September 11, 2014 6:12 AM
  • The common approach is to adjust the time zone when querying the logs.

    You can use DATEADD() in this way:

    SELECT DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), 'HERE goes your date')

    As Olaf correctly states UTC is common for logs and especially for Auditing/Extended Events, and cannot be changed.



    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, September 10, 2014 8:56 AM

All replies

  • Hello,

    Which result do you get when you run this query?

    SELECT GETDATE(), GETUTCDATE()
    The times tamp of log entries are in UTC time.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, September 10, 2014 7:07 AM
  • This is the result2014-09-10 12:18:11.777    2014-09-10 07:48:11.777
    Wednesday, September 10, 2014 7:46 AM
  • So you see, the UTC time is used for log entries.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 10, 2014 7:53 AM
  • Yes, but I need to use the server time!!

    If it is possible please tell me how to change the UTC time to Server time!

    Thanks.

    Wednesday, September 10, 2014 8:01 AM
  • It's common to use UTC for logging and you can't change it for SQL Server.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 10, 2014 8:10 AM
  • The common approach is to adjust the time zone when querying the logs.

    You can use DATEADD() in this way:

    SELECT DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), 'HERE goes your date')

    As Olaf correctly states UTC is common for logs and especially for Auditing/Extended Events, and cannot be changed.



    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, September 10, 2014 8:56 AM
  • Thanks,

    Actually I did not get it.But I have this query to convert the result

    select [statement], 
         CONVERT(datetime, 
            SWITCHOFFSET(CONVERT(datetimeoffset, event_time), 
                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS
          event_time from (
    SELECT event_time,[statement] FROM sys.fn_get_audit_file 
    ('Filepath\AuditFile.sqlaudit'
    ,default,default)
    ) x 

    Thursday, September 11, 2014 6:12 AM
  • Thanks,

    Actually I did not get it.But I have this query to convert the result

    select [statement], 
         CONVERT(datetime, 
            SWITCHOFFSET(CONVERT(datetimeoffset, event_time), 
                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS
          event_time from (
    SELECT event_time,[statement] FROM sys.fn_get_audit_file 
    ('Filepath\AuditFile.sqlaudit'
    ,default,default)
    ) x 


    That works perfectly fine as well :)

    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, September 11, 2014 9:27 AM
  • Thanks to both of you guys.

    :)

    Saturday, September 13, 2014 7:04 AM