How to sort the counts of the sub-category items in the categories?

[homer07]

Hi Splunkers,

My event example is as follows.

fruit_type	size
---------------
apple		big
banana		medium
melon		small
banana		small
apple		small
apple		small
apple		medium
melon		big
melon		big
melon		big

My chart is as follows

How to sort the counts of the sub-category(like size) items in the differ category(such as fruit_type) at once?

Here is my unfinished search.

source="test.csv" sourcetype="csv"
| chart count(size) by fruit_type, size

Thanks for any help.

[gcusello]

Hi @homer07,

let me understand: you want that in each Histogram for each fuit, you have before Small, then Medium and then Big?

This is the sort you want?

If this is your need, use the sort command at the end of your search:

source="test.csv" sourcetype="csv"
| stats count by fruit_type, size
| soer fruit_type -size

In this case it's easy, because the values are in inverse alphabetical order; otherwise you have to use an evall command before the chart adding a number at the start of each value of size, deleting it after the stats command.

Ciao.

Giuseppe

[homer07]

Thank you for your answer, but it is not the result I want. According to your method, in apple’s category, it is sorted ascending, but in melon category, it becomes descending sorting, and I need a consistent sorting in all categories.

[ITWhisperer]

| stats count by fruit_type size
| sort 0 fruit_type count

[homer07]

If I want to sort the results of the chart, what should I do?

[ITWhisperer]

The chart will display the bars in the same order as the columns in the table generated by the chart or xyseries command - this is usually lexicographical - You can transpose the table, sort the rows by some criteria (here I have used the length of the word for the size), then transpose it back.

| makeresults
| eval _raw="fruit_type,size
apple,big
banana,medium
melon,small
banana,small
apple,small
apple,small
apple,medium
melon,big
melon,big
melon,big"
| multikv forceheader=1
| table fruit_type size
| stats count by fruit_type size
| sort 0 fruit_type -count
| xyseries fruit_type size count
| transpose 0 header_field=fruit_type column_name=size
| eval width=len(size)
| sort - width
| fields - width
| transpose 0 header_field=size column_name=fruit_type

[homer07]

Thanks again! In addition, if I want to change "| eval width=len(size)" to "| eval width=count(size)", is it more difficult? Is there other way to replace the effect of chart command?

[ITWhisperer]

The eval you suggested doesn't do what you might think. What is it that you are trying to achieve?

[homer07]

Your method is to calculate the length for different sizes (small, medium, big). What if I want to count the numbers of different sizes? What should I do?

[ITWhisperer]

I am not sure what you are asking for. There are 3 different sizes. Do you simply want the count of how many different sizes were used by fruit_type regardless of how many events there were? Perhaps you could give an example of the desired result (at least in table form) so I can better understand what you are trying to do

[homer07]

Thanks again.

[homer07]

Thank you for your answer, it is the result I want.