topic Re: How to sort the counts of the sub-category items in the categories? in Splunk Search

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

homer07 — Sun, 05 Sep 2021 06:37:42 GMT

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.

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

ITWhisperer — Sun, 05 Sep 2021 09:38:44 GMT

| stats count by fruit_type size | sort 0 fruit_type count

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

gcusello — Sun, 05 Sep 2021 09:39:50 GMT

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

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

homer07 — Sun, 05 Sep 2021 10:12:19 GMT

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.

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

homer07 — Sun, 05 Sep 2021 10:14:22 GMT

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

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

homer07 — Mon, 06 Sep 2021 06:19:22 GMT

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

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

ITWhisperer — Mon, 06 Sep 2021 07:53:52 GMT

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.

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

homer07 — Tue, 07 Sep 2021 00:09:57 GMT

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?

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

ITWhisperer — Tue, 07 Sep 2021 07:24:25 GMT

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

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

homer07 — Tue, 07 Sep 2021 09:06:12 GMT

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?

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

ITWhisperer — Tue, 07 Sep 2021 09:11:44 GMT

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

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

homer07 — Wed, 08 Sep 2021 02:41:28 GMT

Thanks again.