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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...