- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nice, I was working on a similar solution and had not seen your update yet.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
