How to calculate the ratio of fieldA, and if the ratio is greater than 5%, list the top 3 fieldBs associated with fieldA?

[jgcsco]

I have following event:

<...>Status1, StateA<....>
<...>Status2,<...>
<...>Status3<...>
<...>Status1, StateB<...>
<...>Status1, StateC<...>

I need to calculate the ratio of Status1 Count / Total Status Count

| stats count(eval(Status =="Status1")) as Status1_Count, count as Status_Count 
| eval ratio=100*round(Status1_Count/(Status_Count),1)

If the ratio is greater than 5%, I need to find out top 3 States associated with Status1. I have been using "append" to do another search get the result for State, but wondering if there is a way to combine the search into one?

Thanks in advance.

[sideview · Accepted solution]

Assuming that you have fields named Status and State,

<search terms> | fillnull State value="no state" | stats count by Status State | eventstats sum(count) as StatusCount by Status | eventstats sum(count) as TotalCount | search Status="Status1" | eval percent=100*StatusCount/TotalCount | sort - count 

This search first uses fillnull to fill in a "no state" value for the State field if it's missing. We don't care about events that don't have any Status value, but presumably we don't want to exclude the events that have a Status value but no State value.

Then stats gets the unique combinations of Status and State. Then it gets interesting. Eventstats is a lot like stats, except that it leaves the rows untransformed. Instead it paints its output field values onto the same rows. Anyway, eventstats makes a pass through the data, and on each row it will add a field statusCount, that is the number of events in the entire set that have the status value of that row. Then eventstats makes a second pass through, writing on each row a TotalCount field that is, you guessed it, the total count of all events.

Now a search clause narrows us down to only the rows where Status="Status1", an eval calculates our ratio for us, and we sort so that the most common State values will be at the top. If you want the search to return zero results if the radio is less than 5%, that's pretty easy. Also if you want to present the three top States differently.

[jgcsco]

Thanks so much for the clear and detailed explanation. Really helped me understand difference between stats and eventstats.

[jgcsco]

Wonder if I can ask a continuing question regarding the above result output, here is what I have:

| where percent > 5 | table percent State count

And the output is like the following: e.g. percent=5.2

percent State count
5.2 State1 A
5.2 State2 B
5.2 State3 C

Since the percent here is the total percent, I would like the result to show as the following:

percent 5.2
State count
State1 A
State2 B
State3 C

Or:

State count percent 5.2
State1 A
State2 B
State3 C

Thanks