Splunk Search

Search not calculating percentages when a join subsearch is being used

Path Finder

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

0 Karma
1 Solution

Ultra Champion

This should work.

... | eval perc = emailclicked / totalemail * 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

View solution in original post

0 Karma

Ultra Champion

This should work.

... | eval perc = emailclicked / totalemail * 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

View solution in original post

0 Karma

Path Finder

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

0 Karma