Splunk Search

How do I format a number with commas in a column/field that has numbers and strings(using appendpipe)

HattrickNZ
Motivator

How do I format a number with commas in a column/field that has numbers and strings(using appendpipe)

I have the following search:

 | makeresults 
  | eval data = "
  1    2017-12    A    155749    131033    84.1;
 2    2017-12    B    24869    23627    95;
 3    2017-12    C    117618    117185    99.6;
 " 
  | makemv delim=";" data 
  | mvexpand data
  | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
  | fields + date serial type attempts successfullAttempts sr 
  | rename date as _time 
  | search serial=*
  | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"] 

Which gives me the below:

    _time serial type attempts successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869    23627   95
3   2017-12 3   C   117618  117185  99
4                             average sr 92.7

What I want to do is format the columns attempts and successfullAttempts, to have commas in their numbers. But because average sr falls in the successfullAttempts column I am having trouble. By placing the formatting before the append pipe, my "average sr" is removed or does not appear in the successfullAttempts column. **Can I make it so it stays there and the formatting?

...
      | fieldformat attempts=tostring(attempts,"commas")
      | fieldformat successfullAttempts=tostring(successfullAttempts,"commas")
      | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"] 

This gives me the following: (note the text "average sr" has been removed from the successfulAttempts column)

    _time serial type attempts successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869    23627   95
3   2017-12 3   C   117618  117185  99
4                                        92.7
0 Karma

493669
Super Champion

If its not a specific requirement to have average sr present under successfullAttempts column then you can try to have average sr present under different column:

| makeresults 
   | eval data = "
   1    2017-12    A    155749    131033    84.1;
  2    2017-12    B    24869    23627    95;
  3    2017-12    C    117618    117185    99.6;
  " 
   | makemv delim=";" data 
   | mvexpand data
   | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
   | fields + date serial  attempts successfullAttempts type sr 
   | rename date as _time 
   | search serial=*
   | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval type="average sr"]
   | fieldformat attempts=tostring(attempts,"commas")
   | fieldformat successfullAttempts=tostring(successfullAttempts,"commas")

HattrickNZ
Motivator

tks but I want to be able to have numbers and text in the same column, in general. the eval command mentioned in my comments does this. But your idea of re-ordering the columns and putting "average sr" in a column with strings is a good idea.

0 Karma

HattrickNZ
Motivator

I think this is what I am looking for:

...
eval attempts=tostring(attempts,"commas") | 
eval successfullAttempts=tostring(successfullAttempts,"commas") | 
appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"]  | 

eval instead of fieldformat does not seem to present this problem. If anyone could suggest why that would be great? tks

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...