locked
Converting IIS Log Time to Local Time RRS feed

  • Question

  • User531388329 posted

    I thought I once read that there was a way to use logparser to convert the iis times in the iis files to local time.  As you all know this would help greatly in troubleshooting.  I thought I saw it on this site but I can't locate it again.  Any help would be greatly appreciated.

    Tuesday, August 21, 2007 2:21 AM

All replies

  • User531388329 posted

    From the Help File :


    TO_LOCALTIME ( timestamp <TIMESTAMP> )

    Converts a timestamp from Universal Time Coordinates (UTC) time into local time.

    Arguments:

    timestamp:
    UTC timestamp to convert

    Return type:

    TIMESTAMP

    Remarks:

    • The conversion performed by the TO_LOCALTIME function employs the system timezone and daylight saving offset that were or will be in effect at the moment represented by the argument timestamp.

    Examples:

    Retrieve events generated in the past two days:
    SELECT * FROM SYSTEM WHERE TimeGenerated >= TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP('3', 'd') ) )


     Also from http://www.larkware.com/Comments/ShowComments.aspx?SiteID=2&ThreadID=111

    I have an issue translating the UTC date in IISW3C files to local time. Using the following returns the same date time that is in the log file (UTC):

    SELECT TO_LOCALTIME( TO_TIMESTAMP( date, time ) )

    Investigating further I found that SYSTEM_UTCOFFSET() was returning NULL probably causing TO_LOCALTIME not to work! The workaround (hack?) was:

    SELECT ADD( TO_TIMESTAMP( date, time ), TO_TIMESTAMP( '10', 'hh' ) )

    Where '10' is the timezone I want to convert the date/time to.

    Any ideas why SYSTEM_UTCOFFSET is NULL?

    Posted by  William Bartholomew  at  10/25/2003 6:20:45 PM

    Thursday, December 8, 2005 5:41 AM
  • User531388329 posted

    Hi, does anyone have an affective time format? I have tried a few things on this forumn but nothing seems to work when I try to convert UTC to my local server time that is Central time.  I am using 2.2

    SELECT TO_DATE( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS date,
           TO_TIME( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS time,
           s-ip, c-ip, cs-uri-stem, sc-bytes, cs-bytes, time-taken, sc-status

    Please help with this conversion as I am not sure how to replace the Time format when building my CSV file, thank you in advance.

    Monday, July 10, 2006 10:14 PM
  • User531388329 posted

    Try this:

    LogParser "SELECT TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp, c-ip, cs-username, EXTRACT_TOKEN(cs-uri-stem,-1,'/') AS filename INTO DATAGRID FROM \\WEBSERVER\admin$\system32\logfiles\w3svc1\ex07*.log"

    It worked for me.

    Friday, May 11, 2007 11:08 AM
  • User-1887869959 posted

    Where does the output of Log Parser go to?

    "C:\Programs\Log Parser 2.2\LogParser" "SELECT TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp, c-ip, cs-username, EXTRACT_TOKEN(cs-uri-stem,-1,'/') AS filename INTO DATAGRID FROM C:\inetpub\logs\LogFiles\W3SVC1\u_ex13*.log"

    Thank you,

    Docfxit

    Saturday, December 14, 2013 6:03 PM
  • User-1450458553 posted

    I would recommend changing the log format in IIS to record the time in local time rather then UTC. IIS defaults the log format to W3C, which logs the time in UTC.

    Select the web site in IIS then select Features View then select Logging and then change the log format from W3C to IIS or NCSA. Both record the time in local time.

    Thursday, May 5, 2016 3:32 PM
  • User130294145 posted

    Select the web site in IIS then select Features View then select Logging and then change the log format from W3C to IIS or NCSA. Both record the time in local time

    Thanks for this great advice.  I do see this setting on the Default Website but not on my FTP Site...  It is still doing W3C logging, even though I've configured IIS logging on the default site.  How do I apply the IIS logging to my FTP site?

    Thursday, May 19, 2016 4:26 PM
  • User-1790278369 posted

    Hello,

    I had a similar requirement, in IIS "time" comes in GMT time. My requirement was to see information in EST time.CS-URI-stem

    The query below converts GMT to EST time. I used To_Time() function and subtracted (with SUB() function) 5 hours from GMT, as EST is 5 hour behind of GMT.

     .\LogParser.exe -i:IISW3C "select top 30 CS-URI-stem as URI, time-taken, time as GMT-Time, TO_Time(sub(time,timestamp('05:00:00','hh:mm:ss'))) AS EST-Time from 'C:\Folder\Customer\*.log' order by time-taken DESC" -rtp:-1

    Output Example :

    URI Time-taken GMT-Time EST-Time
    /IE-EN/Pages/default.aspx 2703641 19:45:22 14:45:22
    /Documents/somevideo.mp4 2701075 13:40:52 8:40:52
    /Pages/default.aspx 1036878 16:01:56 11:01:56
    /hr-files/my/shared+document/photos.zip 1005513 14:34:55 9:34:55
    Thursday, February 9, 2017 7:09 AM