Summary Stats for a Subsearch

[lehrfeld]

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

[somesoni2 · Accepted solution]

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]

This was exactly what I needed, thanks!

[lehrfeld]

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