Summary results for a "per item" query

[SplunkUser001]

Hello,

I have the following query to search Proofpoint logs. 

index=ppoint_prod host=*host1*
| eval time=strftime(_time, "%m-%d-%y %T")
| rex "env_from\s+value=(?<sender>\S+)"
| rex "env_rcpt\s+r=\d+\s+value=(?<receiver>\S+)"
| stats first(time) as Date first(ip) as ConnectingIP first(reverse) as ReverseLookup last(action) last(msgs) as MessagesSent count(receiver) as NumberOfMessageRecipients first(size) as MessageSize1 first(attachments) as NumberOfAttachments values(sender) as Sender values(receiver) as Recipients first(subject) as Subject by s
| where Sender!=""

It provides what I need on a per message level. How would I modify this to get list of ConnectingIP and ReverseLookup values per Sender. If possible it would be nice to also get number of messages per sender, but it is not absolutely neccessarry. I understand I will need to drop from the query everything  that is message specific like Subject, NumberOfAttachments etc.

I am looking to get something like this:

sender1@domain.com	ConnectingIP_1	ReverseLookup_1
	ConnectingIP_2	ReverseLookup_2
sender2@domain.com	ConnectingIP_3	ReverseLookup_3

[SplunkUser001]

Thanks again

[SplunkUser001]

Thank you guys. This forum is really great.

[marnall]

Assuming you have your table filled with data on a per-message level, you could use stats to get the count per sender and/or connecting IP.

Let's assume each row on your table has a valid connectingIP but not always a sender email address, then give the sender email a fillnull value to make the stats work for rows that don't have a Sender value:

<yoursearch>
| fillnull Sender value="(no sender)"
| stats count by Sender ConnectingIp
| lookup dnslookup clientip as ConnectingIp output clienthost as ReverseLookup

[SplunkUser001]

Thank you marnall. I will try this approach and report back.

[ITWhisperer]

It would be easier for us to help you if you could share some anonymised sample events in a code block (using the </> button above)

[SplunkUser001]

Hi,

I posted sample log entries. I am not sure how readable this is.

[ITWhisperer]

Thanks - what is s in your search by clause as it doesn't appear to be in your data?

[PickleRick]

@ITWhispererLook closer. There is an s=identifier pair in the event 😉

[ITWhisperer]

Thanks @PickleRick I didn't see that and it wasn't (specifically extracted) in the search

[SplunkUser001]

Nov 24 15:01:43 pphost.company.com 2024-11-24T04:01:43.100466+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv mod=session cmd=disconnect module= rule= action= helo=sendinghost msgs=1 rcpts=2 routes=allow_relay,default_inbound,internalnet duration=0.128 elapsed=100
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.614350+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mail cmd=msg module= rule= action=continue attachments=0 rcpts=2 routes=allow_relay,default_inbound,internalnet size=5441 guid=jAIwVNBFVxC8EycWPq7c1MicIX5v1om5 hdr_mid=<42y9nt2euv-1@pphost.company.com> qid=4AO403EC022673 hops-ip=10.20.30.40 subject="MESSAGE SUBJECT" duration=0.125 elapsed=0.127
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.614025+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 cmd=send profile=mail qid=4AO403EC022673 rcpts=RECIPIENT1@company.com,RECIPIENT2@company.com
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.505939+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=judge module=none rule=none
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.505617+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=dmarc cmd=run arc_result=none arc_result_detail=none arc_trusted_flag=0 arc_override=0 dmarc_detail="nothing to see here" dmarc_record=none dmarcverified= final_dmarc_result=none orig_dmarc_result=none auth_result=none original_auth_result= dyndmarc_override_id= dmarcoverride_type=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.505346+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=dkimv cmd=run rule=none dkimresult=none spfheaderfromresult=none duration=0.000
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.505036+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=spf cmd=run cmd=eob result=none
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.504665+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mail cmd=attachment id=0 file=text.html mime=text/html type=html omime=text/html oext=html corrupted=0 protected=0 size=3550 virtual=0 sha256=11dbefae8a521d127ef990b45e998cae68184d56a3d657ee6661f11a8b048d85 a=0 duration=0.000
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.499983+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mimelint cmd=getlint warn=0
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.499957+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mimelint cmd=getlint mime=1 score=0 threshold=100 duration=0.000
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.499913+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mimelint cmd=getlint lint=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.497214+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=headers hfrom=noreply@company.com routes= notroutes=*
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.495249+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=data rcpt_routes=default_inbound rcpt_notroutes=journal data_routes= data_notroutes=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.494969+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=data rcpt=recipeint2@company.com suborg=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.494950+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=data rcpt=recipient1@company.com suborg=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.494892+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=session cmd=data from=noreply@company.com suborg=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.489936+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mail cmd=env_rcpt r=2 value=recipient2@company.com orcpt=RECIPIENT2@company.com verified= routes=default_inbound notroutes=journal
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.488974+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mail cmd=env_rcpt r=1 value=recipient1@company.com orcpt=RECIPIENT1@company.com verified= routes=default_inbound notroutes=journal
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.487458+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv m=1 x=42y9nt2euv-1 mod=mail cmd=env_from value=noreply@company.com ofrom=NoReply@company.com size= smtputf8= qid=42y9nt2euv-1 tls= routes= notroutes=tls_fallback host=sendinghost.company.com ip=10.20.30.40
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.486235+00:00 pphost filter_instance1[1523]: info s=42y9nt2euv mod=mail cmd=helo value=sendinghost extended=1 routes=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.484673+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv mod=session cmd=resolve host=sendinghost.company.com resolve=ok reverse=sendinghost.company.com routes=allow_relay notroutes=
Nov 24 15:00:03 pphost.company.com 2024-11-24T04:00:03.065376+00:00 pphost filter_instance1[1523]: rprt s=42y9nt2euv mod=session cmd=connect ip=10.20.30.40 country=** lip=50.60.70.80 prot=smtp:smtp hops_active=f routes=internalnet notroutes=firewallsafe,outbound,pp_spoofsafe,spfsafe,tls,xclient_trusted perlwait=0.002

[ITWhisperer · Accepted solution]

Assuming s has already been extracted 😎

| eval time=strftime(_time, "%m-%d-%y %T")
| rex "env_from\s+value=(?<sender>\S+)"
| rex "env_rcpt\s+r=\d+\s+value=(?<receiver>\S+)"
| stats first(time) as Date first(ip) as ConnectingIP first(reverse) as ReverseLookup last(action) last(msgs) as MessagesSent count(receiver) as NumberOfMessageRecipients first(size) as MessageSize1 first(attachments) as NumberOfAttachments values(sender) as Sender values(receiver) as Recipients first(subject) as Subject by s
| where Sender!=""
| stats list(ConnectingIP) as ConnectingIP list(ReverseLookup) as ReverseLookup by Sender