Converting IIS Log Time to Local Time

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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?

Answer 7

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