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

View solution in original post

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
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!