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