« Previous Next »

Thread: Converting IIS Log Time to Local Time

Last post 05-11-2007 11:08 AM by LogParser User : epoh977. 3 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (4 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 11-08-2005, 7:33 AM

    Converting IIS Log Time to Local Time

    Reply Contact

    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.
    Tags:

  • 12-08-2005, 5:41 AM In reply to

    RE: Converting IIS Log Time to Local Time

    Mark as Answer
    Reply Contact

    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

    Tags:

  • 07-10-2006, 10:14 PM In reply to

    RE: Converting IIS Log Time to Local Time

    Mark as Answer
    Reply Contact

    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.

    Tags:

  • 05-11-2007, 11:08 AM In reply to

    RE: Converting IIS Log Time to Local Time

    Mark as Answer
    Reply Contact

    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.
    Tags:
Page 1 of 1 (4 items)
Microsoft Communities