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 |
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
Thanks, @ITWhisperer ! That's exactly what I needed!
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