Splunk Search

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

paritoshs24
Explorer
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 ?

Labels (2)
0 Karma
1 Solution

PickleRick
Ultra Champion

Works for me.

See this runanywhere example:

PickleRick_0-1652427830629.png

 

View solution in original post

0 Karma

PickleRick
Ultra Champion

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.

paritoshs24
Explorer

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

0 Karma

PickleRick
Ultra Champion

Works for me.

See this runanywhere example:

PickleRick_0-1652427830629.png

 

0 Karma

paritoshs24
Explorer

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.

0 Karma

PickleRick
Ultra Champion

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.

0 Karma

paritoshs24
Explorer

Thanks for the solution.

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

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>