Sorting String with Number in Splunk Table

[ashish9433]

Hi,

As far as i know Splunk does not have inbuilt functionality to convert/format number in 10000 as 10K or 1000000 as 1M. So i wrote few eval statements which does the task and i am able to get the result as required.

In the Image above, i have sorted the 2nd column (Val1) and then converted into K's & M's format and it pretty well serves the purpose.

But the issue is, unlike as in the image above i have many columns Val2, Val3..... Val11, with so many rows and whenever i click on the header (Val2, Val3.. or so on) to sort the sort happens on the basis of String. Like if click on Val3 header the sorting will happen like K first then M and then the number or the number, then M and then K

Is there a way/feature by which the sorting happens in a custom or user defined way where in the way in the search query i have sorted before converting into K & M format, i could do so when clicking on the table headers.

[sundareshr]

Try using fieldformat for formatting your field http://docs.splunk.com/Documentation/Splunk/6.3.5/SearchReference/Fieldformat. This retains the original value and should help with the sort.

[ashish9433]

Hi Sundareshr,

Thanks for your revert. I understand that fieldformat can be used to retain the orignal value of field but i am not able to apply or may be it is not working in my query. Below is my query can you help me to point where exactly and what change do i need to do.

index = abc | rename "Name" as Group  | lookup parents Node as Group | eval parentsSplit=split(Parents,"|") | table "Group", parentsSplit, "Critical Count" | search parentsSplit="*_OS*" | rename "Critical Count" as new | join type=outer Group [search index = abc | rename "Name" as Group  | lookup parents Node as Group | eval parentsSplit=split(Parents,"|") | table "Group", parentsSplit, "Critical Count" | search parentsSplit="*_OS*" | rename "Critical Count" as old] | fillnull VALUE=0 | eval delta=new-old  | eval delta_perc=if(old==0,round((delta/1)*100,2),round((delta/old)*100,2)) | eval delta_perc = if(abs(delta_perc)>1000000,round(delta_perc/1000000,1)."M",if(abs(delta_perc)>1000,round(delta_perc/1000,1)."K",delta_perc)) | eval result=if((abs(new)>1000000),round(new/1000000,1)."M",if((abs(new)>1000),round(new/1000,1)."K",round(new,2)))." (".if ((abs(delta)>1000000),if(round(delta/1000000,0)>0,"+".round(delta/1000000,1)."M",round(delta/1000000,1)."M"),if((abs(delta)>1000),if(round(delta/1000,0)>0,"+".round(delta/1000,1)."K",round(delta/1000,1)."K"),if(round(delta,2)>0,"+".round(delta,2),round(delta,2))))."/".delta_perc."%)" | table Group, result | rename result AS "Val1"

The output of above search query is as below

Group | Val1
A |18.4K (-2.3K/-10.99%)
B |1.1M (+217.1K/25.38%)
C |313.6K (+236.0K/303.90%)
D |3.4M (+2.0M/148.51%)
E |44.00 (+22.00/100.00%)

So now when i click on Val1 i want it to get sorted as M -> K -> Number && Number -> K -> M

Can you help me where and how fieldformat will work in the above query?

Thanks!