How to match fields with the same name from two events and if match add a field

[SplunkUser001]

Hello,

I have these two events that are part of a transaction.

These have the same s and qid. I need to match s and qid of these two and insert a field equal to hdr_mid from the second event into first event. Is this possible?

In final stats I group events by hdr_mid and qid so I need hdr_mid value present in first event if I want to extract all recipients email addresses.  To do so I need to pull rcpts from first event and not  the second. How would I do that?

Oct 24 13:46:56 hostname.company.com 2024-10-24T18:46:56.426217+00:00 hostname filter_instance1[31332]: rprt s=42cu1tr3wx m=1 x=42cu1tr3wx-1 cmd=send profile=mail qid=49O9Yi2a005119 rcpts=1@company.com,2@company.com,3@company.com...52@company.com

Oct 24 13:46:56 hostname.company.com 2024-10-24T18:46:56.426568+00:00 hostname filter_instance1[31332]: rprt s=42cu1tr3wx m=1 x=42cu1tr3wx-1 mod=mail cmd=msg module= rule= action=continue attachments=0 rcpts=52 routes=allow_relay,default_inbound,internalnet size=4416 guid=Rze4pxSO_BZ4kUYS0OtXqLZjW3uHSx8d hdr_mid=<103502694.595.1729795616099.JavaMail.psoft@xyz123> qid=49O9Yi2a005119 hops-ip=x.x.x.x subject="Message subject" duration=0.271 elapsed=0.325

[ITWhisperer · Accepted solution]

| eventstats values(hdr_mid) AS hdr_mid by s qid

[PickleRick]

Ok. I recognize filterd logs. What is your business case here?

[SplunkUser001]

Hi,

Thank you guys. This helped a lot.

I am sorry for late reply. I was away for a weekend.

The primary business case is to count number of emails and their sizes (grouped by sender's SMTP address) sent from Proofpoint SER to internal SMTPs. The secondary case is to get message level information about these messages (from, to, number of recipients, subject, size). These are two independent Splunk queries.

[SplunkUser001]

Hi gcusello,

Thanks for a quick reply.

Unfortunately this approach in its entirety will not work as there are more events that these two in a "send an email" group of events. All events except the first one from the two I posted have both hdr_mid and qid fields so I group them by these fields in stats. Also, only these two events i posted have rprt set of fields with s in them. I was thinking about somehow matching these two events on s and qid so I can insert field with hdr_mid value into first event. This will allow me to have all events with hdr_mid and qid in them so grouping by hdr_mid and qid in final stats statement will allow to pull list of recipients.

BTW, the values statement below is exactly what I was looking for to pull rctps field from proper event.

values(eval(if(cmd="send",rcpts,""))) AS rcpts

[yuanliu]

---

I was thinking about somehow matching these two events on s and qid so I can insert field with hdr_mid value into first event. This will allow me to have all events with hdr_mid and qid in them so grouping by hdr_mid and qid in final stats statement will allow to pull list of recipients.

---

This is why you need to describe the full use case including all relevant data, not just those you are trying to extract something.

@gcusello's idea is still applicable here; you just substitute stats with eventstats.

<your-search>
| eventstats
     values(hdr_mid) AS hdr_mid 
     values(eval(if(cmd="send",rcpts,""))) AS rcpts
     BY s qid
| stats whatever by hdr_mid qid

[gcusello]

Hi @SplunkUser001 ,

you can do this using stats, something like this:

<your-search>
| stats
     values(hdr_mid) AS hdr_mid 
     values(eval(if(cmd="send",rcpts,""))) AS rcpts
     BY s qid

Ciao.

Giuseppe