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

[jwalzerpitt]

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

[sundareshr · Accepted solution]

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

[jwalzerpitt]

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

[sundareshr]

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

[jwalzerpitt]

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

[sundareshr]

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 

[jwalzerpitt]

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

[sundareshr]

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

[jwalzerpitt]

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!

[sundareshr]

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

[jwalzerpitt]

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

[jwalzerpitt]

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

[jwalzerpitt]

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

Thx again