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

[0p3r4t0r8089]

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

[0p3r4t0r8089]

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

[ITWhisperer · Accepted solution]

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