Search not calculating percentages when a join subsearch is being used

[lehrfeld]

All - I have what I originally thought was a simple problem. I needed to calculate a percentage from two values in a row.

For example, my current output looks like

Repeat Phish Campaign   Total_Emails  E-Mails_clicked    percentage
2                           1000            100            
4                           2000            400
6                           3000            1500

I can't seem to get the search to calculate the percentage between the total and clicked emails. Here is the current search that generates the above output.

sourcetype=phishing_recipients repeat 
| stats count as phishingRcvd by campID 
| join type=outer max=0 campID [search sourcetype=phishing_clickers] 
| stats first(phishingRcvd) as Total_E-mails_Sent, count(userID) as E-Mails_Clicked by campID
| eval percentage = E-Mails_Clicked * 100 / Total_E-mails_Sent
| rename campID as "Repeat Phish Campaign" 
| table "Repeat Phish Campaign", Total_E-mails_Sent, E-Mails_Clicked, percentage

I am raising the white flag on this one...

Thanks! Mike

[kristian_kolb · Accepted solution]

This should work.

... | eval perc = email_clicked / total_email * 100

If you are not getting any results at all for the percentage, I would recommend you to rename your fields, so that they do not contain hyphens, i.e. Total_E_mails_Sent instead of Total_E-mails_sent etc..

/K

[lehrfeld]

Yup - great call. Splunk did not like the underscore in the field names. 3 hours of my life I won't get back 😉 Thanks! Mike