Splunk Search

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

homer07
Explorer

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

sort.png

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.

Labels (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

homer07
Explorer

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats count by fruit_type size
| sort 0 fruit_type count

homer07
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

homer07
Explorer

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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

homer07
Explorer

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?

Tags (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

homer07
Explorer

Thanks again.

0 Karma

homer07
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...