Sort by alternative column

[lindonmorris]

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:

item	size_pretty
disk 1	2 GB
disk 2	1 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

[cklunck · Accepted solution]

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:

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]

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 🙂

[yeahnah]

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

[yeahnah]

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]

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

[yeahnah]

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]

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.

[yeahnah]

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.