track email using sendmail logs over multiple relay jumps

[DanneFo]

We are trying to find a way to track email that goes through more than one relay, but haven't found a way yet. Yes, we are quite new to Splunk.

Goal: show "from", "to" and other fields for an email passing through several relays, per relay.

We tried this, which is close but not quite right:

sourcetype=sendmail_syslog qid=* [search sourcetype=sendmail_syslog relay="*google.com" | fields msgid ] | transaction qid | table _time qid from to nrcpts host arg1

The only (should be) unique field connecting an email transaction on relay1 and relay2 is "msgid", so this should work but it only gets the msgid line of each transaction. The entire log line with "to" is missing from the results. The "transaction qid" does not help.

What did we miss?

Log example:

Jun 14 09:43:01 relay1 sendmail[93821]: u5E7h032096841: from=<from@domain.com>, size=4479, class=0, nrcpts=1, msgid=<uniquie-msgid-001mail.gmail.com>, proto=ESMTP, daemon=MTA, relay=mail-qg0-f44.google.com [209.85.192.44]
Jun 14 09:43:01 relay1 sendmail[94832]: u5E7h032096841: to=<to@domain.com>, delay=00:00:00, xdelay=00:00:00, mailer=esmtp, pri=124479, relay=mailserver.domain.com. [22.33.44.55], dsn=2.0.0, stat=Sent (Ok: queued as 4283441)
Jun 14 09:43:02 relay2 sendmail[10865]: u5E7h2Lu010855: from=<from@domain.com>, size=5773, class=0, nrcpts=1, msgid=<uniquie-msgid-001mail.gmail.com>, proto=ESMTP, daemon=MTA, relay=mailserver.domain.com [22.33.44.55]
Jun 14 09:47:37 relay2 sendmail[11976]: u5E7h2Lu010855: SMTP outgoing connect on relay2.ministry.se
Jun 14 09:47:37 relay2 sendmail[11987]: u5E7h2Lu010855: to=<to@domain.com>, delay=00:04:35, xdelay=00:00:00, mailer=smtp, pri=125773, relay=internalmta.domain.com. [11.22.33.44], dsn=2.0.0, stat=Sent (<uniquie-msgid-001mail.gmail.com> [InternalId=03849873487] Queued mail for delivery)
Jun 14 09:47:37 relay2 sendmail[11978]: u5E7h2Lu010855: done; delay=00:04:35, ntries=1

[DanneFo · Accepted solution]

I'll answer my own question.

This solved it our problem; two subsearches:

sourcetype=sendmail_syslog [ search sourcetype=sendmail_syslog [ search sourcetype=sendmail_syslog relay="*google.com" | fields msgid] | fields qid ] | transaction qid | table _timestamp qid msgid from to nrcpts host relay stat