Splunk Search

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

0p3r4t0r8089
Explorer

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
Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

0p3r4t0r8089
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...