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
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
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
1-4-2006 158 425 1444
2-4-2006 472 580 1471
3-4-2006 217 3891 1515
4-4-2006 90 2939 1503
