What stats/eval to use to find results of email recipients who clicked a link?

0p3r4t0r8089 — Wed, 15 Mar 2023 18:28:48 GMT

Hello,

I'm building a report to list all phishing and malware threat detections by sender, classification, and threat url. The data contains two types of events "clicksAllowed" and "clicksBlocked". I want to add a list of recipients if their click was allowed "clicksAllowed" and I'm struggling with how to structure my query. I'm currently trying to do this with stats and eval (I thought about using subsearch too maybe), hopefully, I'm on the right track but I can't figure out how to show only the recipients who clicked while still showing counts of how many clicks were allowed and blocked.

Current search (without who clicked):

index=tap sourcetype="pp_tap_siem" classification IN (phish, malware) threatStatus=active
| eval time=strftime(_time,"%m/%d/%y @ %H:%M:%S")
| stats earliest(time) AS First_Seen, latest(time) AS Last_Seen count(eval(eventType="clicksPermitted")) AS Clicks_Permitted, count(eval(eventType="clicksBlocked")) AS Clicks_Blocked, values(threatURL) AS TAP_Link BY sender, classification, url
| table First_Seen, Last_Seen, classification, sender, Clicks_Permitted, Clicks_Blocked, AT_Risk_Users, url, TAP_Link
| sort -Last_Seen

Output looks like:

First_Seen	Last_Seen	classification	sender	Clicks_Permitted	Clicks_Blocked	AT_Risk_Users	url	TAP_Link
03/14/23 @ 17:52:36	03/14/23 @ 17:52:36	phish	badguy@domain.com	1	1	list of 1 person here	hxxp://baddomain.com	hxxp://link_tothreatintel_webportal.com/uniqueguid
01/05/23 @ 12:34:44	01/05/23 @ 17:44:41	phish	badguy2@domain.com	39	3	list of 39 people here	hxxp://baddomain2.com	hxxp://link_tothreatintel_webportal.com/uniqueguid
01/18/23 @ 15:43:20	02/16/23 @ 22:46:19	malware	badguy3@domain.com	4	0	list of 4 people here	hxxp://baddomain.com	hxxp://link_tothreatintel_webportal.com/uniqueguid

Re: Stats / Eval to find results of email recipients who clicked a link

ITWhisperer — Wed, 15 Mar 2023 15:24:26 GMT

Assuming your recipient field is called recipient, try something like this

| stats earliest(time) AS First_Seen, latest(time) AS Last_Seen count(eval(eventType="clicksPermitted")) AS Clicks_Permitted, count(eval(eventType="clicksBlocked")) AS Clicks_Blocked, values(eval(if(eventType=="clicksPermitted",recipient,null()))) as recipients values(threatURL) AS TAP_Link BY sender, classification, url

Re: Stats / Eval to find results of email recipients who clicked a link

0p3r4t0r8089 — Wed, 15 Mar 2023 16:31:10 GMT

Thanks, @ITWhisperer ! That's exactly what I needed!

topic Re: Stats / Eval to find results of email recipients who clicked a link in Splunk Search

What stats/eval to use to find results of email recipients who clicked a link?

Re: Stats / Eval to find results of email recipients who clicked a link

Re: Stats / Eval to find results of email recipients who clicked a link