topic Re: How to edit my search to calculate percentage of a multivalued field for total by Country? in Splunk Search

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

jwalzerpitt — Thu, 19 May 2016 17:13:24 GMT

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

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

sundareshr — Thu, 19 May 2016 17:35:01 GMT

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

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

jwalzerpitt — Thu, 19 May 2016 17:45:50 GMT

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

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

sundareshr — Thu, 19 May 2016 17:59:17 GMT

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

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

jwalzerpitt — Thu, 19 May 2016 18:06:01 GMT

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

Thx again

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

jwalzerpitt — Thu, 19 May 2016 18:15:00 GMT

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

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

sundareshr — Thu, 19 May 2016 18:48:10 GMT

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

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

jwalzerpitt — Thu, 19 May 2016 19:08:36 GMT

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:

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

I would expect to see 15% for percentage

Thx

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

jwalzerpitt — Thu, 19 May 2016 19:12:28 GMT

Changed the query to:

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

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

sundareshr — Thu, 19 May 2016 19:49:24 GMT

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

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

jwalzerpitt — Thu, 19 May 2016 19:56:15 GMT

Much better as the query now reads:

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

Thx

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

jwalzerpitt — Thu, 19 May 2016 20:37:13 GMT

Changed from eventstats to stats and good to go now:

as I see one line showing Canada at 15.22%.

Thx for helping me work through this! Greatly appreciated!

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

sundareshr — Thu, 19 May 2016 20:41:40 GMT

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