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.
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.
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.
Does it work if you remove the fieldformat?
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.
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
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!
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.
Please share your query.
@sundareshr updated the search query.