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

[feickertmd]

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.

[lguinn2 · Accepted solution]

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.

[feickertmd]

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

[aholzer]

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.

[feickertmd]

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.

[aholzer]

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.

[feickertmd]

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

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

[aholzer]

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

[feickertmd]

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.