Why is my query using the transaction command missing some events?

[silverlink34]

Here is ALL of the data that is actually in the logged transaction:

Nov 1 15:41:18 mail qmail: 1541101278.677067 new msg 1048820 (19a97640-de0e-11e8-a1a3-f74156095854)
Nov 1 15:41:18 mail qmail: 1541101278.677088 info msg 1048820: bytes 5247 from <fromemail@fdomain.com> qp 1854 uid 64011
Nov 1 15:41:18 mail qmail: 1541101278.677096 starting delivery 626443: msg 1048820 to local tdomain.com-recipient1@todomain.com
Nov 1 15:41:18 mail qmail: 1541101278.677113 starting delivery 626444: msg 1048820 to remote recipient2@trdomain.com
Nov 1 15:41:18 mail qmail: 1541101278.677131 starting delivery 626445: msg 1048820 to remote recipient3@trdomain.com
Nov 1 15:41:18 mail qmail: 1541101278.739330 delivery 626443: success: mlpid_1862/did_0+0+1/
Nov 1 15:41:20 mail qmail: 1541101280.703177 delivery 626444: success: 0.0.0.1_accepted_message./Remote_host_said:_250_ujhK1y00D03Y9o001jhLWU_mail_accepted_for_delivery/
Nov 1 15:41:31 mail qmail: 1541101291.640138 delivery 626445: success: 0.0.0.2_accepted_message./Remote_host_said:_250_OK_id=1gIIqQ-001DRc-3G/
Nov 1 15:41:31 mail qmail: 1541101291.640224 end msg 1048820

I have 2 field extractions: msg_id that pulls value "1048820" and delivery_id that pulls the values: 626443,626444,626445 for each instance.

When I search

> host = "1.1.1.1"| transaction msg_id startswith="new msg" endswith="end msg" maxpause=1h connected=false

My transactions are grouped together properly however the lines with "delivery $delivery_id$: success:......" are not present.
Example:

Nov 1 15:41:18 mail qmail: 1541101278.677067 new msg 1048820 (19a97640-de0e-11e8-a1a3-f74156095854)
Nov 1 15:41:18 mail qmail: 1541101278.677088 info msg 1048820: bytes 5247 from <fromemail@fdomain.com> qp 1854 uid 64011
Nov 1 15:41:18 mail qmail: 1541101278.677096 starting delivery 626443: msg 1048820 to local tdomain.com-recipient1@todomain.com
Nov 1 15:41:18 mail qmail: 1541101278.677113 starting delivery 626444: msg 1048820 to remote recipient2@trdomain.com
Nov 1 15:41:18 mail qmail: 1541101278.677131 starting delivery 626445: msg 1048820 to remote recipient3@trdomain.com
Nov 1 15:41:31 mail qmail: 1541101291.640224 end msg 1048820

I am piping the transactions into a table like so:

> host = "1.1.1.1"| transaction msg_id startswith="new msg" endswith="end msg" maxpause=1h connected=false | table qmail_from_address, qmail_to_address, qmail_delivery_status, msg_id, delivery_id _time

But since the status messages are not present in the transaction search, the qmail_delivery_status column is empty.

Curious enough, when I change my transaction search to include delivery_id as a field, I only get ONE delivery in it, with the status, and the other delivered recipients do not show up.
Example:

Nov 1 15:41:18 mail qmail: 1541101278.677067 new msg 1048820 (19a97640-de0e-11e8-a1a3-f74156095854)
Nov 1 15:41:18 mail qmail: 1541101278.677088 info msg 1048820: bytes 5247 from <fromemail@fdomain.com> qp 1854 uid 64011
Nov 1 15:41:18 mail qmail: 1541101278.677131 starting delivery 626445: msg 1048820 to remote recipient3@trdomain.com
Nov 1 15:41:31 mail qmail: 1541101291.640138 delivery 626445: success: 0.0.0.2_accepted_message./Remote_host_said:_250_OK_id=1gIIqQ-001DRc-3G/
Nov 1 15:41:31 mail qmail: 1541101291.640224 end msg 1048820

If I can figure out how to get the delivery status lines in my transaction search, my table will populate correctly.

What am I doing wrong?

[renjith_nair · Accepted solution]

@silverlink34,

Give this a try,

host = "1.1.1.1"| rex field=_raw "msg (?<msg_id>\d+)"|rex field=_raw "delivery (?<delivery_id>\d+)"
|eventstats values(msg_id) as msg_id by delivery_id
|transaction msg_id startswith="new msg" endswith="end msg" maxpause=1h connected=false

[silverlink34]

Awesome thank you! That works great.