Dashboards & Visualizations

Sort by alternative column

lindonmorris
Explorer

I have the following query displaying as a table in a classic dashboard:

 

 

| makeresults format=json data="[{\"item\":\"disk1\", \"size\":2147483648, \"size_pretty\":\"2 GB\"}, {\"item\":\"disk2\", \"size\":1099511627776, \"size_pretty\":\"1 TB\"}]"
| table item size size_pretty

 

 


Now when you sort by "size" the table works as expected (2GB is smaller than 1TB).

When you sort by "size_pretty" though, it of course will think that "1 TB" is first in order, followed by "2 GB" (lexicographic sort order).

 

What I would like however (purely about user experience) is to 

1) Hide the "size" column as it will be pretty horrible to read

2) When the user clicks the "size_pretty"  column to sort the table, I want it to actually sort by "size" (up or down) - Even though that column is not visible to the user, meaning the output (sorted smallest to largest) would look like:

itemsize_pretty
disk 12 GB
disk 21 TB

 

Is there any way to achieve this? Note that I am on Splunk Cloud, so I do not have access to the file system.

 

(if it can be done on a dynamic dashboard instead, i'd consider that)

 

Bonus points if I can also apply column formatting with a colour scale as you would on a normal table

 

Labels (1)
0 Karma
1 Solution

cklunck
Path Finder

I'm not sure if it's possible to sort by a different column. However...there is a way to display the "size" field in a pretty format, while retaining the underlying numerical data for sorting. This could work as long as you're not married to using the "size_pretty" from your JSON.

Here's an example search (with some additional data I added to test the sorting):

| makeresults format=json data="[{\"item\":\"disk1\", \"size\":2147483648, \"size_pretty\":\"2 GB\"}, {\"item\":\"disk2\", \"size\":1099511627776, \"size_pretty\":\"1 TB\"}, {\"item\":\"disk3\", \"size\":660, \"size_pretty\":\"660 B\"}, {\"item\":\"disk4\", \"size\":20147483648, \"size_pretty\":\"18 GB\"}, {\"item\":\"disk5\", \"size\":1047548, \"size_pretty\":\"1,023 KB\"}]" 

| fieldformat size=printf("%4u",round(size/pow(1024,if(size=0,0,floor(min(log(size,1024),10)))),2)).case(size=0 OR log(size,1024)<1," B", log(size,1024)<2," KB", log(size,1024)<3," MB", log(size,1024)<4," GB", log(size,1024)<5," TB", log(size,1024)<6," PB", log(size,1024)<7," EB", 1=1, " EB")

| table item size size_pretty

 

This outputs a table like the following. It sorts the size column as expected, while retaining the pretty formatting:

cklunck_0-1690254668075.png

You can remove the "size_pretty" field from the | table command if you no longer wish to display it.

The color scale is up to you, but I think it might still be do-able since the underlying numeric data is still there.

This answer was inspired by the following post:  Smart conversion of large numbers to human-readable format (Splunk Answers) 

View solution in original post

cklunck
Path Finder

I'm not sure if it's possible to sort by a different column. However...there is a way to display the "size" field in a pretty format, while retaining the underlying numerical data for sorting. This could work as long as you're not married to using the "size_pretty" from your JSON.

Here's an example search (with some additional data I added to test the sorting):

| makeresults format=json data="[{\"item\":\"disk1\", \"size\":2147483648, \"size_pretty\":\"2 GB\"}, {\"item\":\"disk2\", \"size\":1099511627776, \"size_pretty\":\"1 TB\"}, {\"item\":\"disk3\", \"size\":660, \"size_pretty\":\"660 B\"}, {\"item\":\"disk4\", \"size\":20147483648, \"size_pretty\":\"18 GB\"}, {\"item\":\"disk5\", \"size\":1047548, \"size_pretty\":\"1,023 KB\"}]" 

| fieldformat size=printf("%4u",round(size/pow(1024,if(size=0,0,floor(min(log(size,1024),10)))),2)).case(size=0 OR log(size,1024)<1," B", log(size,1024)<2," KB", log(size,1024)<3," MB", log(size,1024)<4," GB", log(size,1024)<5," TB", log(size,1024)<6," PB", log(size,1024)<7," EB", 1=1, " EB")

| table item size size_pretty

 

This outputs a table like the following. It sorts the size column as expected, while retaining the pretty formatting:

cklunck_0-1690254668075.png

You can remove the "size_pretty" field from the | table command if you no longer wish to display it.

The color scale is up to you, but I think it might still be do-able since the underlying numeric data is still there.

This answer was inspired by the following post:  Smart conversion of large numbers to human-readable format (Splunk Answers) 

lindonmorris
Explorer

That's perfect, thanks @cklunck .

I had actually originally started using fieldformat, but for some reason switched to using eval (same/similar printf statements).

 

I didn't understand that it doesn't affect the underlying data, just the display of - So the sort works perfectly.

 

Many thanks 🙂

0 Karma

yeahnah
Motivator

Nice, I was working on a similar solution and had not seen your update yet.

0 Karma

yeahnah
Motivator

Hi @lindonmorris 

Have a look at this answer

https://community.splunk.com/t5/Splunk-Search/How-do-I-hide-a-column-in-a-table/m-p/425273

In a dashboard table you can define which columns are visible.   I don't think you can use colours if the scale is different, e.g. GB and TB

Hope that helps.

lindonmorris
Explorer

Thanks @yeahnah , that lets me hide the column, but rather than being able to control the drilldown, i'd like to control the sort behaviour

Tags (1)
0 Karma

yeahnah
Motivator

Hi @lindonmorris 

Yeah, it would not meet your second requirement to sort on the size_pretty column, when clicked.  For that you need to look at javascript, which starts getting complicated.  Not recommended.

The only other way is to use a consistent size scale.

| makeresults format=json data="[{\"item\":\"disk1\", \"size\":2147483648, \"size_pretty\":\"2 GB\"}, {\"item\":\"disk2\", \"size\":1099511627776, \"size_pretty\":\"1 TB\"}]"
| eval size_pretty_gb=round(size/1024/1024/1024, 2)
| table item size_pretty_gb

You could then define colour scales too.

Hope that helps
 

 

lindonmorris
Explorer

Thanks @yeahnah , while my example would kinda work in that case, the numbers will be significantly different (varying from Bytes to Petabytes) so the dynamic unit is a necessity for readability.

0 Karma

yeahnah
Motivator

OK,  I think I have a workable solution using the fieldformat command.  Here's an example

| makeresults
| eval size=split("2147483648:2GB,1099511627776:1TB,1125899906842624:1PB", ",")
| mvexpand size
| eval size_pretty=mvindex(split(size, ":"), 1), size=mvindex(split(size, ":"), 0)
| fieldformat size=size_pretty

 
Basically, size shows the size_pretty value but the sort uses the size value to do the column sort order.

Give it ago and see if it meets your requirements.  It will not help with colour scale mind you.

0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...