All Apps and Add-ons

Summary Stats for a Subsearch

lehrfeld
Path Finder

Hi All - I am working my first Splunk project and I have come across what I believe to be a simple problem, but I can't seems to solve it. I have two sourcetypes - Responses are people who have clicked on a Phisihing email; and Submitters who have submitted the Phishing email to the SPAM mailbox. What the query below does is simply return the users who have click the link in the Phish but have NOT submitted it to the SPAM mailbox.

sourcetype=Response_Detail NOT [search sourcetype=Submitters UserID=* | table UserID] | stats count AS "Clicked but did not Submit"

What I would like to do is also return the total number of users who have clicked - sourcetype=Response_Detail |stats count(UserID) - and provide a pretty pie chart or something like it. But I can't seem to capture this stat with my query.

Thanks for any and all help!

Mike

Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

Could try this and let me know if it works for you.

sourcetype=Response_Detail | table UserID | eval joinfield=1 | join type=outer UserID [search sourcetype=Submitters UserID=* | table UserID |eval joinfield=0] | stats count(eval(joinfield=0)) as "Clicked but did not Submit", count as TotalClicks

View solution in original post

somesoni2
Revered Legend

Could try this and let me know if it works for you.

sourcetype=Response_Detail | table UserID | eval joinfield=1 | join type=outer UserID [search sourcetype=Submitters UserID=* | table UserID |eval joinfield=0] | stats count(eval(joinfield=0)) as "Clicked but did not Submit", count as TotalClicks

cbowles
Explorer

This was exactly what I needed, thanks!

0 Karma

lehrfeld
Path Finder

Totally awesome. I am learning so much from these forums. Thank you Somesoni2.

Only change is to change the joinfield=0 to a 1 in the stats function. when joinfield=0 is returns the people that clicked on a phish AND reported it. joinfield=1 returns just the people that clicked on a phish but did not report it.
Thanks!
Mike

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...