Converting IIS Log Time to Local TimeRSS

7 replies

Last post Feb 09, 2017 07:09 AM by Surath

  • Converting IIS Log Time to Local Time

    Nov 08, 2005 07:33 AM|Anonymous|LINK

    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.

    IIS Input Format

  • Re: Converting IIS Log Time to Local Time

    Dec 08, 2005 05:41 AM|Anonymous|LINK

    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

    IIS Input Format

  • Re: Converting IIS Log Time to Local Time

    Jul 10, 2006 10:14 PM|Anonymous|LINK

    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.

    IIS Input Format

  • Re: Converting IIS Log Time to Local Time

    May 11, 2007 11:08 AM|Anonymous|LINK

    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.

    IIS Input Format

  • Re: Converting IIS Log Time to Local Time

    Dec 14, 2013 06:03 PM|Docfxit|LINK

    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

    IIS Input Format

  • Re: Converting IIS Log Time to Local Time

    May 05, 2016 03:32 PM|MauricioFeijo|LINK

    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.

  • Re: Converting IIS Log Time to Local Time

    May 19, 2016 04:26 PM|adporter|LINK

    MauricioFeijo

    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?

  • Re: Converting IIS Log Time to Local Time

    Feb 09, 2017 07:09 AM|Surath|LINK

    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