Splunk Search

Use subsearch to calculate stats as well as provide input to main search

lehrfeld
Path Finder

Hello Splunkers -

I have phishing data that we would like to report on. I have two sourcetype - clickers (people who clicked on a particular campaign) and recipients (list of people who were sent the phishing emails, some of which are 'repeat' campaigns)

sourcetype=clickers
fields: userID campID ...

sourcetype=recipients
fields: userID campID ...

The recipients event may contain the keyword 'repeat'. This is important.

I would like to search the recipients sourcetype for the keyword 'repeat'. I would like to count the number of users for each campID that are associated with 'repeat'. Then we would like to use the identified campIDs from the recipients sourcetype and use those to get the count of users who actually clicked on those campaigns.

So, we have campID 14,16,and 20 with the 'repeat' keyword from the recipients sourcetype. I want to know how many users these campaigns were sent to. So we have

campID count
14 1000
16 2000
20 3000

Next, I take the campID and use that to count the number of people that actually clicked on those specific campaigns from the clickers sourcetype. The final result would look like this.

campID count click_count
14 1000 100
16 2000 200
20 3000 300

Here is the start of my search... but I can only get one set or the other, not both.

sourcetype=clickers | join campID [search sourcetype=recipients repeat] | eval campID{sourcetype}=campID | stats count(eval (campIDclickers)) as CLICKERS, count(eval(campIDrecipients)) as TOTAL_EMAILS by campID

Thanks everyone!

Mike

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try following

sourcetype=recipients repeat | stats count as phishingRcvd by campID | join type=outer max=0 campID [search sourcetype=clickers] | stats first(phishingRcvd) as count_recieved, count(userID) as count_clicked by campID

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try following

sourcetype=recipients repeat | stats count as phishingRcvd by campID | join type=outer max=0 campID [search sourcetype=clickers] | stats first(phishingRcvd) as count_recieved, count(userID) as count_clicked by campID
0 Karma

lehrfeld
Path Finder

Worked like a champ. Thank you... now I need to study up on this! Mike

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...