Summary of emails with bounce info in a single file.

[zydfm]

Im seeking help on how I can improve my method of email and bounce correlation and hoping this could be improved substantially, as this has been so very troublesome for me doing this on a daily basis.

Ill extract mails with a specific subject and date - average lines per extraction reaches up to 24k+, Since our ESA cannot extract more than 24k to CSV, i have to do another set of extraction by typing in the last row date and perform a search again.

here's an example (message tracking output)

This reaches up to 24k lines, when it reaches this high, this would indicate (for me) that there are more logs to extract.
so what i'd do, is convert the last row date from GMT to GMT+8 (since the search function accepts GMT+8) and place that date on the "end date".

it'll then produce another set of logs to extract, ill do this until there aren't anymore logs to get. sometimes it would reach 5-7 parts all 24k lines each.

once i have all those logs, ill consolidate them into 1 sheet. so it'd reach up to 100k++ lines.
Now the complex part (at least for me) - for each row i need to correlate the lines from bounce logs based on MID.

Because bounce logs provides an easy-to-understand description - and the people i send this report with, want it on layman's term.

So id go to System Ad > Log Subscription > Bounces/ and download the appropriate date.

Since above date is at 7-27, id then look for bounces with the same date

e.g line

Sat Jul 27 09:11:28 2019 Info: Bounced: DCID 96353744 MID 199298654 From:<> To:<.....> RID 0 - 5.1.0 - Unknown address error ('554', ['5.7.1 <....>: Recipient address rejected: Destination .Domain does not exist or has expired. Please check for typographical errors in the recipient email address.'])

I need this detail above to be correlated in my emails extracted via message tracking, then ill perform a "vlookup" function in excel to correlate based on MID value.

so the output would be something like this

Is there an easy way for me to do this without downloading separate files from message tracking and bounces/ and correlate them manually in excel?

This process will usually take me for about 2-3 hours depending on the amount of logs, excel would crash sometimes bec it cant handle the data when performing sorting / ordering.

was hoping i could make this process easier. If only message tracking can extract lines more than 24k+ i'd be easier for me to get all logs without performing date conversion and perform search method.

[Mathew Huynh]

Hello Zydfm,

I tried to look for an option on our devices but not able to find an alternative means to meet the requirement.
Message tracking export currently shows the sender, recipient and last state only (if it was delivered or aborted).

You can probably run an extended grep on mail_log files to extract the log lines you need, but you'll likely need to run another script or tool to correlate the details into a user friendly file.

Else - I would imagine filing an Enhancement request for more customization on tracking/exports might be required.

Regards,
Mathew

[zydfm]

few questions,

1) Is there a way to change the default GMT when extracting email tracking logs to GMT +8? so i can skip the manual conversion.
2) "You can probably run an extended grep on mail_log files to extract the log lines you need" can you provide me the correct syntax for this? i find the email tracking search function a bit clunky at times.

something like this (e.g)
GREP for
Date: between 7-20-19 to 7-25-19
Subject that contains: Jul 20
Recipient begins with: test@test.com

and output

Date - MID - Sender - Recipient - Subject - Last state

[Mathew Huynh]

Hey Zydfm,
Typically GUI -> System Admin -> Timezone and change it to the relevant location should update the tracking as well.

As per extended grep, we cannot control the output to be in the exact line format sought as grep is essentially just finding outputs which meet the expression being searched and print the raw line.
Grep isn't able to be used to correlate multiple lines into events, and you will still need to manually extract and modify.

Regards,
Mathew

[zydfm]

timezone is already set to our local time - only when i extract the logs the time format changes to GMT by default.

here's the email tracking result its on GMT+8

but when i extract all these logs, the output converts to GMT.

[Mathew Huynh]

Hey Zydfm,
I was hoping that's not the case - I'll likely need to check into my lab boxes to verify if it's possible to change the export data time setting.
Regards,
Mathew