topic Re: How to display only subsets of data from correlated sendmail log transactions? in Splunk Search

How to display only subsets of data from correlated sendmail log transactions?

mailwimp — Fri, 27 Jan 2023 19:04:03 GMT

The sender and recipient information I need from Unix/Linux "sendmail" logs is contained in separate lines in the sendmail log. I am able to correlate all the entries for a given email using nested search, dedup, and transation using the following search:

index="sendmail_logs" host=relay* [search index="sendmail_logs" host=relay* from=\<*@example.com\> | dedup qid | fields qid ] | transaction fields=qid maxspan=1m

which produces the following (simplified and obfuscated):

2023-01-26T23:37:25+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter=ourmiltname, action=mail, continue
2023-01-26T23:37:25+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter=ourmiltname, action=rcpt, continue
2023-01-26T23:37:25+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter=ourmiltname, action=data, continue
2023-01-26T23:37:25+00:00 relay1 sendmail[115877]: 30QNbOpD115877: from=<bounce+e1165d.ef30-username=ourdomain.com@example.com>, size=25677, class=0, nrcpts=1, msgid=<20230126233721.b60dfcd8b6c1249b@example.com>, bodytype=8BITMIME, proto=ESMTPS, daemon=MTA, tls_verify=NO, auth=NONE, relay=m194-164.mailgun.net [161.38.194.164]
2023-01-26T23:37:26+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter add: header: X-NUNYA-SPF-Record: v=spf1 include:mailgun.org include:_spf.smtp.com ~all
2023-01-26T23:37:26+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter change: header Subject: from Sample Subject Line to EXTERNAL: Sample Subject Line
2023-01-26T23:37:26+00:00 relay1 sendmail[115877]: 30QNbOpD115877: milter=ourmiltname, action=eoh, continue
2023-01-26T23:37:27+00:00 relay1 sendmail[115887]: 30QNbOpD115877: to=<username@ourdomain.com>, delay=00:00:02, xdelay=00:00:01, mailer=smtp, tls_verify=OK, pri=145677, relay=nexthop.ourdomain.com. [192.168.0.7], dsn=2.0.0, stat=Sent (30QNbQau230876 Message accepted for delivery)
2023-01-26T23:37:27+00:00 relay1 sendmail[115887]: 30QNbOpD115877: done; delay=00:00:02, ntries=

Now, what I want to do is reduce the output to only the lines that contain the strings "from=" OR "to=". I am new to splunk, so i tried adding adding

| regex _raw="from\=\<|to\=\<"

but all the lines are still displayed. Suggestions on how to correct my query?

Re: How to display only subsets of data from correlated sendmail log transactions?

scelikok — Fri, 27 Jan 2023 19:10:26 GMT

Hi @mailwimp,

If you need only from and to fields, you can try below;

index="sendmail_logs" host=relay* | stats values(from) as from values(to) as to by qid

Re: How to display only subsets of data from correlated sendmail log transactions?

mailwimp — Fri, 27 Jan 2023 19:23:59 GMT

Tried that, but by I need to query against a specific sending domain and need the complete set of data on the "from=" and "to=" lines to be able to provide transaction tracking data to an info security team member that needs it as part of his investigation.

Re: How to display only subsets of data from correlated sendmail log transactions?

yuanliu — Sun, 29 Jan 2023 07:08:17 GMT

Do you mean

index="sendmail_logs" host=relay* (TERM(from=) OR TERM(to=)) [ search index="sendmail_logs" host=relay* from=\<*@example.com\> | dedup qid | fields qid ] | transaction fields=qid maxspan=1m

Re: How to display only subsets of data from correlated sendmail log transactions?

mailwimp — Mon, 30 Jan 2023 07:58:57 GMT

@yuanliu Your solution wass very close - and got me pointed in right direction that solved my issue and provided the results I needed . Needed to add quotation marks around the TERM searches so that query would work ; i.e. :

(TERM("from=") OR TERM("to="))