My query is with resp to failure reasons corresponding to hard / soft bounce stats. Actually I am not directly using adobe MTA, but built my custom MTA through which deliveries are happening. From these logs, I am mapping success / failures and then dumping into 'NmsBroadLogRcp' and 'NmsBroadLogMsg' tables.
'NmsBroadLogMsg' table deals with failure reasons and types but corresponds at operator level instead of individual recipients associated with delivery.
Here's what am doing,
1. I scheduled a delivery, and related details based on my custom MTA logs were inserted in 'NmsBroadLogRcp' table 2. After recomputing, 'NmsDeliveryLogStats' table also got updated with related success / failures 3. Now I manually inserted entries in the 'NmsBroadLogMsg' table with failureReason 5. After recomputing, 'NmsDeliveryLogStats' table also got updated with related failure reason but against all domains
I updated 'NmsBroadLogMsg' manually as other than 'ibroadlogmsgid', there are no unique identifiers based on which I could run SQL insert/update operations. Could someone suggest how I could proceed from here, given that 'ibroadlogmsgid' value corresponds to operator associated deliveries? So what-ever deployments carried out by an operator, all would have the same 'ibroadlogmsgid' as against 'ideliveryID' existing in 'NmsBroadLogRcp' table. Inserting/updating based on msgID won't serve my purpose since I won't know on what and against which delivery the operation is carried out. Neither would md5 column would help since it also seems to have same value for all recipients associated with a delivery.
Also how its been done at adobe end, do they have a nighty job running in the background that internally maps to associated deliveryIds before updating the said table one by one?
Since I am updating the 'NmsBroadLogMsg' table manually, any operation would overwrite the existing one in absence of unique filter/column, I would need help as to how different failure reasons could be captured/associated for each recipient? e.g. for a delivery of 5 emails, 3 failed because of mailbox full while 2 for the unknown user. At the moment with my manual stuff, I could only apply the same failure reason against all.
Could someone help me with the blockers I am stuck with...
My question is why are you using the broadLogMsg table to map it with recipient ?
By default and by design , the broadLogRcp is linked N:1 with recipients and should be used as source of truth to determine failures , reason of failure and to see if a delivery to a recipient was successful.
BroadLogMsg is used as a reference to map a failure and error message in the broadLogRcp table.