topic rounding percentage comes back blank, adding commas ruins sorting in Splunk Search

rounding percentage comes back blank, adding commas ruins sorting

jericksonpf — Thu, 03 Jan 2013 18:42:16 GMT

Hi,
I am using a query that uses the awesome percentage value feature built into stats. It outputs into a table that my coworkers get in a report. They love the report but they hate the formatting. This has lead me to two issues:

1) I have managed to figure out how to round most of the values into easily readable formats, but when I try and round the percentage outputs using feildformat they come back blank. Below is an example where I have tried a feildformat on 99% but not on 95%. 99% disappears There is no need for that decimal point at all, how can i get rid of it while keeping the output? Note: the values are for a duration and are indeed all numerical.

95%Ms 99%Ms

2000.0000

2) Looking at the same example above my coworkers really want commas in the larger numbers, when i run this query for anything more than a few hours the total cost is in the billions. So i tried using the tostring(bytes, "commas") strategy to break up the numbers, but this ruins the table because I am no longer able to sort by total cost. Splunk seems to only want to account for the values before the first comma so 600,000 will be sorted right below 601,987,543,123.

Here is the search i use:

eventtype="Event" 
| stats min(xtime) as Min, avg(xtime) as AvgDuration, median(xtime) as Median, p95(xtime) as 95%Ms, 
          p99(xtime) as 99%Ms, max(xtime) as Max, count(xmethod) as NumCalls , sum(xtime) as TotalCost 
          by xmethod 
| eval TotalCost = (NumCalls * AvgDuration) 
| fieldformat Min=round(Min) 
| fieldformat AvgDuration=round(AvgDuration) 
| fieldformat 99%Ms=round(99%Ms) 
| fieldformat Median=round(Median) 
| fieldformat Max=round(Max) 
| fieldformat TotalCost=round(TotalCost) 
| sort -TotalCost | head 20

Thank you so much for all your help doing my job for me!

Splunk is the best!

Re: rounding percentage comes back blank, adding commas ruins sorting

lguinn2 — Thu, 03 Jan 2013 20:47:10 GMT

Try this

eventtype="Event" 
| stats min(xtime) as Min, avg(xtime) as AvgDuration, median(xtime) as Median, p95(xtime) as 95Ms, 
          p99(xtime) as 99Ms, max(xtime) as Max, count(xmethod) as NumCalls , sum(xtime) as TotalCost 
          by xmethod 
| eval TotalCost = (NumCalls * AvgDuration) 
| eval Min=round(Min) 
| eval AvgDuration=round(AvgDuration) 
| eval 99Ms=round(99Ms) 
| eval 95Ms=round(95Ms) 
| eval Median=round(Median) 
| eval Max=round(Max) 
| eval TotalCost=round(TotalCost) 
| rename 99MS as "99% Ms", 95Ms as "95% Ms"
| sort -TotalCost 
| fieldformat TotalCost = string(TotalCost,"commas")
| head 20

I did a few things differently. First, eval changes both the value and the representation for a field; fieldformat should change only the representation. (This doesn't seem to be exactly true, but...) So, I used eval whenever I wanted to change the actual value for the field.

Second, just in case fieldformat was screwing up the sort, I did the sort first and then the fieldformat on TotalCost. The fieldformat command really shouldn't be screwing up the sort, though.

Finally, and most important - you can't use a % in the name of a field. It doesn't break the stats command, but it silently breaks most other commands, like eval and fieldformat. So I used different names, and renamed the fields to a display-friendly name just before they are output.

Here is the reference to Field Name Syntax.

Re: rounding percentage comes back blank, adding commas ruins sorting

jonuwz — Thu, 03 Jan 2013 21:31:06 GMT

Interestingly the linked docs state that a field name cannot start with 0-9 or _ ?

Re: rounding percentage comes back blank, adding commas ruins sorting

jericksonpf — Thu, 03 Jan 2013 21:32:36 GMT

WOW thank you so much for helping me out AGAIN lguinn you just saved me several hours of productivity and frustration. you are a lifesaver and really good at this splunk stuff.

So what you posted above did not exactly work for me but it got me on the right path. I ditched the percentages, but the search was coming back with an error "eval command malformed expected )" So i took out the numbers and just used all text for the fields and it worked!!!!.
Secondly above you just used the string command and my version of splunk didn't like that so i used tostring after the sort as you suggested and everything is looking fantastic!!! Thanks so much!!!

Here is what i ended up with:

eventtype="Event" | stats min(xtime) as Min, avg(xtime) as AvgDuration, median(xtime) as Median, p95(xtime) as ninetyfive, p99(xtime) as ninetynine, max(xtime) as Max, count(xmethod) as NumCalls , sum(xtime) as TotalCost by xmethod | eval TotalCost = (NumCalls * AvgDuration) | eval Min=round(Min) | eval AvgDuration=round(AvgDuration) | eval Median=round(Median) | eval ninetyfive=round(ninetyfive) | eval ninetynine=round(ninetynine) | eval Max=round(Max) | eval TotalCost=round(TotalCost) | sort -TotalCost | fieldformat TotalCost = tostring(TotalCost,"commas") | head 20

Re: rounding percentage comes back blank, adding commas ruins sorting

jericksonpf — Thu, 03 Jan 2013 21:37:57 GMT

jonuwz you are right letters need to go first. I have now changed my fields to p95 p99 and it works

Re: rounding percentage comes back blank, adding commas ruins sorting

lguinn2 — Fri, 04 Jan 2013 01:53:04 GMT

Thanks jonuwz - I should read my own links!!