Converting IIS Log Time to Local Time
Last post May 11, 2007 04:08 PM by Anonymous
Nov 08, 2005 12:33 PM|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
Dec 08, 2005 10:41 AM|Anonymous|LINK
From the Help File :
Converts a timestamp from Universal Time Coordinates (UTC) time into local time.
SELECT * FROM SYSTEM WHERE TimeGenerated >= TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP('3', 'd') ) )
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
SELECT TO_LOCALTIME( TO_TIMESTAMP( date, time ) )
SELECT ADD( TO_TIMESTAMP( date, time ), TO_TIMESTAMP( '10', 'hh' ) )
Jul 11, 2006 03:14 AM|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.
May 11, 2007 04:08 PM|Anonymous|LINK
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
It worked for me.