Splunk Search

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

paritoshs24
Path Finder
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
SplunkTrust
SplunkTrust

Works for me.

See this runanywhere example:

PickleRick_0-1652427830629.png

 

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Works for me.

See this runanywhere example:

PickleRick_0-1652427830629.png

 

0 Karma

paritoshs24
Path Finder

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
SplunkTrust
SplunkTrust

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.

paritoshs24
Path Finder

Thanks for the solution.

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...