Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to calculate the ratio of fieldA, and if the r...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

jgcsco

Path Finder

03-30-2015
11:50 AM

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.

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

sideview

SplunkTrust

03-30-2015
01:46 PM

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.

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

jgcsco

Path Finder

03-30-2015
03:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

jgcsco

Path Finder

04-08-2015
03:06 PM

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