asked on

SQL Syntax for Log Parser Time Date functions - Exchange 2003 Tracking Logs

Hello - I am trying to use Log Parser 2.2 to review Exchange 2003 Tracking Logs.

I want to compare the Date and Time fields to the Origination-Time field, as you can see in the Date and Time fields are separate and the Origination-Time combines the date and time.

Can someone give me advice on how I would be able to compare the two values (Date, Time) and (Origination-Time), I am looking for the difference in seconds.

http://anewmessagehasarrived.blogspot.com/2008/03/exchange-2003-statistics-with-logparser.html

Example Data:
Date 2010-3-15
Time 0:0:19 GMT
Origination-Time 2010-3-14 23:55:49 GMT

Thanks,
Mark

Anthony Perkins

This has nothing to do with MS SQL Server, for better response I suggest you get a Moderator to change that zone to SQL Query Syntax zone:
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/

Having said that, please post your query as it stands now.

markpalinux

ASKER

This query got me the local time and removed the "gmt" , not sure how to get a diff of those time stamps via sql syntax, currently looking at powershell to read the csv and find the differences in seconds between the two times.

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" -i:W3C -o:CSV "Select TO_LOCALTIME(TO_Timestamp(REPLACE_STR(STRCAT(STRCAT(date,' '), time),' GMT',''),'yyyy-M-d h:m:s') ) as DateTime,MSGID,TO_LOCALTIME(TO_TIMESTAMP(REPLACE_STR(Origination-Time,' GMT',''),'yyyy-M-d h:m:s')) as ForOrigTime INTO BigReport3.csv from C:\Temp\exchange_tracking\20100315.log WHERE Event-ID IN (1027;1028)"

BigReport3.csv
DateTime,MSGID,ForOrigTime
2010-03-14 20:00:19,36119119-3jdsk-23jjj-sdad-23jksdjk3423@gmail.com,2010-03-14 19:55:49

Thanks,
Mark

ASKER CERTIFIED SOLUTION

Ted Bouskill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

markpalinux

ASKER

Thank you for the information.