topic Re: stats list() command will not display items after fieldformat. How can I resolve it? in Splunk Search

stats list() command will not display items after fieldformat. How can I resolve it?

feickertmd — Fri, 23 Jan 2015 15:27:39 GMT

This a fairly big query, so only run it for the past couple of weeks.

index=_internal source=*license_usage.log type="Usage"|bin _time span=1d
|eval h=lower(h)
| stats sum(b) as b by idx, h, st, _time | stats avg(b) as dailyAverage, max(b) as dailyMax by idx, h,
 st
|appendpipe [stats sum(dailyAverage) as dailyAverage sum(dailyMax) as dailyMax by idx,h | eval st="--TOTAL  IN MB BY HOST--"]
|appendpipe [stats sum(dailyAverage) as dailyAverage sum(dailyMax) as dailyMax by idx | eval st="##-TOTAL  IN MB BY APPLICATION-##"]
|eval dailyAverage=round(dailyAverage/1024/1024,3)
|eval dailyMax=round(dailyMax/1024/1024,3)
| sort idx, h,dailyAverage
|stats  list(h) as h, list(st) as Sourcetype,list(dailyAverage) as "dailyAverage", list(dailyMax) as dailyMax by idx
|rename h as "Server Name", st as "Data Source", dailyAverage as "Daily Data Average in MB", dailyMax as "Maximum Daily Data"

The issue is that I want my fields showing size in MB to have comma separation for readability. We usually obtain that through a search query like this:

|fieldformat dailyAverage= tostring(dailyAverage,"commas")

This query line works until I run a stats command and try to list the values. I end up with a blank dailyAverage column and the corresponding size column continues to show numerical values with no commas. Somehow the list() function is negating the fieldformat command.

Any help appreciated.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

aholzer — Fri, 23 Jan 2015 16:27:02 GMT

Are you doing the formatting before or after the stats?

If you do it before, then it's like converting all your numbers to strings, and trying to calculate the average of a string.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

feickertmd — Fri, 23 Jan 2015 16:30:54 GMT

I have attempted it right before and after line 10. If I add it before line 10 and delete everything after, I get the rendering I want. But when I then apply list(), it dies.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

aholzer — Fri, 23 Jan 2015 16:34:55 GMT

What you want to do is apply the formatting after you do the the stats command. You should get your average calculated based on the numbers and then convert it to a string.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

feickertmd — Fri, 23 Jan 2015 16:37:16 GMT

Tried it, by adding the fieldformat after stats but before rename.

I supplied the code, it's an internal index, so try it yourself.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

aholzer — Fri, 23 Jan 2015 16:44:28 GMT

Got it. The reason for that is because you have created a multi-value field (mvfield) by using list(), and the fieldformatter doesn't know how to handle those.

You are either going to have to figure out how to manipulate mvfields to iterate through all the values in the mvfield, or you are going to have to calculate the daily average in a step before you use the stats list, so that you can convert it to a formatted string and then put it in the stats list().

Re: stats list() command will not display items after fieldformat. How can I resolve it?

feickertmd — Fri, 23 Jan 2015 16:46:15 GMT

Tried that as well. If you add fieldformat before line 10, it applies to single value fields and works great. But then if you try to aggregate them in list after fieldformat... boom.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

lguinn2 — Mon, 26 Jan 2015 01:48:27 GMT

The problem arises because of how fieldformat works. It creates a "string version" of the field as well as the original (numeric) version. Most likely the stats command is unclear about which version of the field should be used - or something like that. Try this instead:

 index=_internal source=*license_usage.log type="Usage" 
 | bin _time span=1d | eval h=lower(h)
 | stats sum(b) as b by idx h st _time 
 | stats avg(b) as dailyAverage, max(b) as dailyMax by idx, h, st
 |appendpipe [stats sum(dailyAverage) as dailyAverage sum(dailyMax) as dailyMax by idx h 
              | eval st="--TOTAL  IN MB BY HOST--"]
 |appendpipe [stats sum(dailyAverage) as dailyAverage sum(dailyMax) as dailyMax by idx 
              | eval st="##-TOTAL  IN MB BY APPLICATION-##"]
 |eval dailyAverage=tostring(round(dailyAverage/1024/1024,3),"commas")
 |eval dailyMax=round(dailyMax/1024/1024,3)
 | sort idx h dailyAverage
 | stats  list(h) as h list(st) as Sourcetype list(dailyAverage) as dailyAverage list(dailyMax) as dailyMax by idx
 | rename h as "Server Name" st as "Data Source" dailyAverage as "Daily Data Average in MB"
          dailyMax as "Maximum Daily Data"

I can't actually test this completely, but I think it should work.

Re: stats list() command will not display items after fieldformat. How can I resolve it?

feickertmd — Mon, 26 Jan 2015 14:51:27 GMT

That's what I needed, was eval vs. fieldformat. Thanks, lgiunn!