LOGPARSER query to retrieve average transfer time grouped by date

Logparser query to retrieve average transfer time grouped by date of email between MS Exchange servers, additional info average size in KB.
The WHERE statement is filtering only messages in size range between 6 and 7 MB. SORT BY does not work as expected. The partner-name filter is optional and used to exclude inter-bridgehead servers traffic.

SELECT
Date, AVG(TO_INT(SUB(TO_Timestamp(REPLACE_STR(STRCAT(STRCAT(date,’ ‘), time),’ GMT’,”),’yyyy-M-d h:m:s’),TO_Timestamp(REPLACE_STR([Origination-time], ‘ GMT’,”),’yyyy-M-d h:m:s’)))) AS SecondsDiff,
COUNT(*), DIV(AVG(total-bytes),1024) AS AvgKB_6_7MB
INTO ‘D:LogParserApril2Apr_Mail_Between_6_7MB_1019.txt’
FROM F:ExLogserver1200604*.log,
F:ExLogserver2200604*.log,
F:ExLogserver3200604*.log,
F:ExLogserver4200604*.log
WHERE total-bytes BETWEEN 6291456 AND 7340032 AND EXTRACT_TOKEN(Partner-Name, 0, ‘-‘) NOT LIKE ‘%BZ%’ AND Event-ID = ‘1019’
GROUP BY Date
ORDER BY Date
**********************************
Sample output (tab delimited)
Date SecondsDiff COUNT(ALL *) AvgKB_1_2MB
1-4-2006 158 425 1444
2-4-2006 472 580 1471
3-4-2006 217 3891 1515
4-4-2006 90 2939 1503

GoudaCast #13 – What’s Up!

http://krysmann.net/podcast/podcast13.mp3 podcast – What’s Up!
download tu podcast numer 13, 12 minut 8,4 MB
W audycji:
o http://www.filmweb.pl/Film?id=89546 Six Feet Under sierial ktrory pochlania caly moj wolny czas.

– Co holendrzy na temat otwarcia granic dla pracownikow z miedzy innymi polski
– http://pacificcoasthellway.blogspot.com/2006/03/pch211-vatican-spin-machine.html Pacific Coast Hellway
– muzyka http://music.podshow.com/music/listeners/artistdetails.php?BandHash=835a96cfbeeff69de8100818165ccfba Black Lab
– http://biggu.com/ Big in Japan – service dla podcasterow

Bardzo prosze o http://krysmann.net/blog/contact.html uwagi i komentaze dotyczace zawartosci tego podcastu.

Event-ID W3C

D:LogParser>logparser “SELECT DISTINCT Event-ID, COUNT(*) FROM serverserver.log20060403.log GROUP BY Event-ID” -i:w3c -o:tsv
Event-ID COUNT(*)
7 =19418
1019 =2034116
1025 = 34116
1024 =2034116
1033 = 34116
1038 =2021526
1023 = 21727
1029 = 2021679
1036 = 2037
1028 =2061
0 = 15834
1027 =2015965
1020 = 210497
1034 =2015289
1031 = 210497
8 =20304
2 =20140
29 = 367
Statistics:
———–
Elements =
processed:=20
691805
Elements output: 18
Execution=20
time: 37.83 seconds

LogParser

Using Log Parser
You can download the Log Parser kit from the Microsoft Web site
(http://www.microsoft.com/downloads/details.aspx?familyid=3D890cd06b-abf8-4c25-91b2-f8d975cf8c07&display
lang=3Den
). The rudimentary installation procedure, which typically
takes less than a minute, creates the Log Parser 2.2 directory under
program files. This directory contains all the Log Parser files,
including the logparser .exe executable. If you’ve ever used SQL, Log
Parser will be familiar. For example, to count all the messages
received on the local server, I used the following command:

logparser “SELECT COUNT(*) FROM C:tempserver.log WHERE [Event-ID] 1028” –
i:W3C

This command generated the output that Figure 1 shows. All I asked Log
Parser to do is to count the number of records in the log file (i.e.,
C:tempserver.log) to which I pointed the utility. You can pass
parameters such as c:logs*.log to instruct Log Parser to process all
the message-tracking logs in a directory. In the previous example, Log
Parser looks for records with an event ID of 1028, which is the
identifier that Exchange writes into the message-tracking log when it
delivers a message to a local mailbox on a server. The -i:W3C
parameter tells Log Parser that the input file is in World Wide Web
Consortium (W3C) format. This speedy utility can process even very
large message-tracking logs in a flash: When I ran this sample
command, Log Parser processed more than 1,000,000 records in 16.5
seconds. . . .