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

[lehrfeld]

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

[somesoni2 · Accepted solution]

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

[lehrfeld]

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