topic Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri in Splunk Search

How to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with strings?

paritoshs24 — Fri, 13 May 2022 15:42:05 GMT

a	b	c	d	e	f	g
xyz	1	2	3	4	5	6

My table looks like that
I need the following table

a	b	c	d	e	f	g
xyz	1000.00	2000.00	3000.00	4000.00	5000.00	6000.00

but with the below command I am getting the following table :
my string field gets eliminated

my query for data | foreach * [eval <<FIELD>> = round(('<<FIELD>>' * 1000),2) ]

a	b	c	d	e	f	g
	1000.00	2000.00	3000.00	4000.00	5000.00	6000.00

How to obtain everything above along with xyz in a column ?

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

PickleRick — Thu, 12 May 2022 10:13:46 GMT

Firstly, I'm not a big fan of truncating fields or modifying them in any way without a good cause so I'd rather use fieldformat rather than eval (especially if you want to calculate something down the processing stream based on the original values; truncating introduces rounding error).

But I'm digressing.

The easiest thing to do is use coalesce to add the original value in case your computations turned null.

eval <<FIELD>> = coalesce(round('<<FIELD>>'*1000,2),'<<FIELD>>')

As coalesce() evaluates to the first non-null value from the list, for numerical values you'll get your number, for other values you'll get the original field contents.

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

paritoshs24 — Thu, 12 May 2022 22:01:02 GMT

Thanks for the solution.

Can you also please provide the solution with FEILDFORMAT command ? 🙂 😀

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

paritoshs24 — Fri, 13 May 2022 05:54:16 GMT

The coalesce command isn't working, its printing all the values as it is

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

PickleRick — Fri, 13 May 2022 07:44:31 GMT

Works for me.

See this runanywhere example:

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

paritoshs24 — Fri, 13 May 2022 16:10:23 GMT

Sorry, My Bad !!
Worked for me too, it was an error on my part.

Can you also please provide me a solution with field formatting, will keep that in mind for future use cases.

Re: how to change the number formatting to 2 decimals and multiply with 1000 for all fields except the fields with stri

PickleRick — Sat, 14 May 2022 07:25:18 GMT

The fieldformat syntax is almost the same as eval. The difference is that fieldformat doesn't create a new field as such. So you will see the "field" in output but you cannot calculate anything based on its value - you have to use the original field.

See the example (also shows the difference between eval and fieldformat):

| makeresults count=210
| streamstats count
| eval part=count/13
| eval truncated=round(part,1)
| fieldformat formatted=round(part,1)
| eventstats sum(truncated) as truncatedsum sum(part) as partsum

In case of simple multiplying when there's no rounding error, there's not much of a practical difference since the value itself does not differ but in case of fractions the error can accumulate and skew the final results.

The most useful use case of field format is with timestamp manipulation when you leave the original timestamp field in numerical form which allows for easy adding/substracting intervals and so on but still can get a human-readable representation in the results.