• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1830
  • Last Modified:

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.


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

  • 2
1 Solution
Anthony PerkinsCommented:
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:

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

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)"

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

Ted BouskillSenior Software DeveloperCommented:
This URL has all the LogParser functions with some samples that will suit your needs.

Specifically there is a function called QUANTIZE() that you can use to convert a timestamp to seconds to then use in your comparisons

Usually I use LogParser to push logs into a SQL database where I can then use a more feature rich syntax to process the data.
markpalinuxAuthor Commented:

Thank you for the information.

Featured Post

SMB Security Just Got a Layer Stronger

WatchGuard acquires Percipient Networks to extend protection to the DNS layer, further increasing the value of Total Security Suite.  Learn more about what this means for you and how you can improve your security with WatchGuard today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now