topic Re: How to match fields with the same name from two events and if match add a field in Splunk Search

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

SplunkUser001 — Fri, 25 Oct 2024 13:25:10 GMT

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

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

gcusello — Fri, 25 Oct 2024 13:35:19 GMT

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

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

SplunkUser001 — Fri, 25 Oct 2024 15:39:41 GMT

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

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

yuanliu — Fri, 25 Oct 2024 18:34:36 GMT

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

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

PickleRick — Sat, 26 Oct 2024 08:04:03 GMT

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

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

ITWhisperer — Sat, 26 Oct 2024 11:50:20 GMT

| eventstats values(hdr_mid) AS hdr_mid by s qid

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

SplunkUser001 — Mon, 28 Oct 2024 21:22:07 GMT

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.