Splunk Search

How to edit my search to calculate percentage of a multivalued field for total by Country?

Motivator

I'm trying to craft a search that will show the percentage of quarantined messages by country, but I'm struggling a little on how to complete it. I have the following:

index="email" `MACRO` [search sourcetype=csv | iplocation FromIP | stats count by Country | where Country != "United States" | sort 20 -count ] 
| eval success=if(Status="Quarantined",1,0)
| stats count as total sum(success) as success
| eval Percentage=success/total
| table _time Percentage

If I strip out the subsearch, I get the percentage, but trying to figure out how to incorporate iplocation into the search and then break that down per country.

Thx

0 Karma
1 Solution

Legend

Is this what you're looking for?

   index="email" `MACRO` [search sourcetype=csv | iplocation FromIP | where Country != "United States" |  stats count by FromIP | sort 20 -count | table FromIP ] 
    | eval success=if(Status="Quarantined", 1, 0)
    | iplocation FromIP
    | eventstats count as total by FromIP
    | eval avgper=1/total
    | timechart sum(avgper) as Percentage by Country

View solution in original post

Legend

Is this what you're looking for?

   index="email" `MACRO` [search sourcetype=csv | iplocation FromIP | where Country != "United States" |  stats count by FromIP | sort 20 -count | table FromIP ] 
    | eval success=if(Status="Quarantined", 1, 0)
    | iplocation FromIP
    | eventstats count as total by FromIP
    | eval avgper=1/total
    | timechart sum(avgper) as Percentage by Country

View solution in original post

Motivator

Almost and perhaps I left out a key piece in my original question (of which I apologize).

I want to see what the percentage of quarantined messages by country compared to the total amount of messages by country. So for example, we received 100 emails from Canada, of which 10 were quarantined, so 10% of message from Canada are quarantined, and so on for the other countries and then sort from highest to lowest based on percentage.

Does that help clarify what I'm looking to do?

Thx

0 Karma

Legend

Try this change

     ...
     | eventstats count as total sum(success) as success by FromIP
     | eval perc=success/total
     | timechart max(perc) as Percentage max(total) as Total by Country
0 Karma

Motivator

Also to confirm, is this calculating the percentage of quarantined messages by the total number of message per country? The percentages for each country seem to be off.

Thx

0 Karma

Legend

When you say you want to use stats instead of timechart, does that mean you do not what to see time? See which of these you prefer

index="email" `MACRO` [search sourcetype=csv | iplocation FromIP | where Country != "United States" |  stats count by FromIP | sort 20 -count | table FromIP ] 
     | iplocation FromIP
     | eventstats count as total count(eval(Status="Quarantined")) as success by FromIP
     | eval avgper=success/total
     | timechart values(avgper) as Percentage by Country

OR 

index="email" `MACRO` [search sourcetype=csv | iplocation FromIP | where Country != "United States" |  stats count by FromIP | sort 20 -count | table FromIP ] 
     | iplocation FromIP
     | stats count as Total count(eval(Status="Quarantined")) as success by Country
     | eval Percentage=success/Total
     | table Country Total Percentage 
0 Karma

Motivator

Changed the query to:

index="email" | iplocation FromIP | where Country = "Canada" | stats count by Country | sort 20 -count
| eventstats count as Total, count(eval(Status="Quarantined")) as success by Country
| eval Percentage=success/Total

and now I'm seeing the count of 2,444 emails received, but the Total is 1 and no percentage. Seems to have an issue with - count(eval(Status="Quarantined")) as success by Country

0 Karma

Legend

Did you try changing it to the way you had it sum(eval(if(Status="Quarantined", 1, 0)))

0 Karma

Motivator

Changed from eventstats to stats and good to go now:

index="email" | iplocation FromIP | where Country = "Canada"
| stats count as TotalCount, sum(eval(if(Status="Quarantined", 1, 0))) AS Caught by Country
| eval Percentage=(Caught/TotalCount)*100
| table Country Percentage

as I see one line showing Canada at 15.22%.

Thx for helping me work through this! Greatly appreciated!

0 Karma

Legend

If this worked, please mark as answered for others who may have similar requirements.

0 Karma

Motivator

Much better as the query now reads:

index="email" | iplocation FromIP | where Country = "Canada"
| eventstats count as TotalCount, sum(eval(if(Status="Quarantined", 1, 0))) AS Caught by Country
| eval Percentage=(Caught/TotalCount)*100
| table Country Percentage

but I'm seeing the percentage for each event (all 2,444 of them) instead of one event

Thx

0 Karma

Motivator

Correct, I just want to see the table for whatever date/time period I select (for example, past 4 hours).

I still don't think the percentage calculation is working as what i did was focused on just Canada and did a quick query to produce the percentage of quarantined messages received from Canada (372) vs. the total number of email received from Canada (2444) for a defined 30 minutes period, which is 15%.

However, when I run the following query:

index="email" | iplocation FromIP | where Country = "Canada" | stats count by Country | sort 20 -count
| iplocation FromIP
| stats count as Total count(eval(Status="Quarantined")) as success by Country
| eval Percentage=success/Total
| table Country Total Percentage

I see the following table:
Country Total Percentage
Canada 1 0

I would expect to see 15% for percentage

Thx

0 Karma

Motivator

Looks much better, but can I replace timechart with stats?

Thx again

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!