topic Re: Not able to use Fields, Rename, Table command after Fieldformat command in Splunk Search

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

ashish9433 — Tue, 13 Sep 2016 11:16:34 GMT

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.

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

sundareshr — Tue, 13 Sep 2016 11:44:09 GMT

Please share your query.

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

ashish9433 — Tue, 13 Sep 2016 11:57:50 GMT

@sundareshr updated the search query.

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

sundareshr — Tue, 13 Sep 2016 12:15:43 GMT

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.

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

ashish9433 — Tue, 13 Sep 2016 12:39:43 GMT

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.

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

sundareshr — Tue, 13 Sep 2016 19:26:29 GMT

Does it work if you remove the fieldformat?

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

ashish9433 — Thu, 15 Sep 2016 05:49:19 GMT

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.

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

sundareshr — Thu, 15 Sep 2016 13:01:12 GMT

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

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

ashish9433 — Fri, 16 Sep 2016 05:07:50 GMT

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.

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

ashish9433 — Fri, 16 Sep 2016 05:17:11 GMT

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!