Splunk Search

Not able to use Fields, Rename, Table command after Fieldformat command

ashish9433
Communicator

Hi,

I am trying to convert some values with combination of Alphabets, Special Characters and numbers but still want to retain the property to be number so as to be able to sort on numeric values.

The below is an example -> 3.4M (0.00/0.00%)

I am able to do this using fieldformat command, if i use eval command then the numeric property of the field is not retained.

Now the problem is commands like fields, rename, table doesn't work. If any of these commands are used after fieldformat, then the field which was formatted using fieldformat becomes blank.

Can any one guide me, how to overcome this issue.

Note - It is not possible to use fields, rename, table commands before fieldformat in my search query. so need a work around to overcome this issue.

Update - Search Query

index = abc source="2016-09-12" | 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 source="2016-09-05" | 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)) | fieldformat new=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."%)" | 

The above query runs well and gives me the output i want, but once i add the below i get empty result

table Group, new | rename new AS "Val1"

So using table, rename command is not working after fieldformat command.

0 Karma

sundareshr
Legend

My guess is the issue is with the join. Try this updated search without the join. Also, I don't see a result field anywhere in your search. is that coming from the lookup table?

 index = abc (source="2016-09-12" OR source="2016-09-05")
| rename "Name" as Group  
| lookup parents Node as Group 
| eval parentsSplit=split(Parents,"|") 
| table "Group", parentsSplit, "Critical Count" 
| search parentsSplit="*_OS*" 
| eval "Critical Count"=if(source="2016-09-05", "old", "new")
| 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)) 
| fieldformat new=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"

PS: I have not validate the fieldformat command, I assume that is giving correct results.

0 Karma

ashish9433
Communicator

Thanks for your effort and revert.

The above search query also not working.

The result is the way if the below is removed

| table Group, new
| rename new AS "Val1"

So again the problem reaches the same point, wherein once the table or even just the rename command is used the formatting of new done using fieldformat command is lost and the value is empty.

0 Karma

sundareshr
Legend

Does it work if you remove the fieldformat?

0 Karma

ashish9433
Communicator

Yes, instead of fieldformat, if i use eval the result is proper.
But again due to eval i am not able sort, which fieldformat is able to give me.

0 Karma

sundareshr
Legend

What are you sorting on? It appears the fieldformat command is giving an error becuase of the complexiity. Try simplifying it. Ideally, you should do all your sorting BEFORE and then use eval to format for display. If you share you entire search, I can help. But now it is unclear what fields you are using and one you are not.

Also, you should consider using case instead of if

0 Karma

ashish9433
Communicator

Check this link, https://answers.splunk.com/answers/63730/using-fieldformat-and-rename.html

It seems that there is a bug or may be splunk has purposely made it that way!

0 Karma

ashish9433
Communicator

if i sort before and then use eval command, then in that case definetly it will show in sorted format. But as i have mentioned in the question as well, if i click on the field name then the sorting happens as a string because it contains Letters and special characters. Whereas if i use fieldformat, then it works exactly the way i want.

The problem with fieldformat is, it works only when it is the last command in the search query. Incase if try to use fields command or even table command, the value formatted with fieldformat command becomes empty and hence the issue.

0 Karma

sundareshr
Legend

Please share your query.

0 Karma

ashish9433
Communicator

@sundareshr updated the search query.

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 ...