Splunk Search

Sorting String with Number in Splunk Table

ashish9433
Communicator

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.

alt text

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.

0 Karma

sundareshr
Legend

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
Communicator

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!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...