Splunk Search

round number from max (*) as * and also avg(*) as *

mmouse88
Path Finder

I have a created a table using timechart with the max #. It generates a row of maximum of sourcetype. How would I round the max # to 0 decimal. Here's my command:

timechart sum(eval(quantity/12)) span=1h by sourcetype | stats max(*) as *

timechart sum(eval(quantity/12)) span=1h by sourcetype | stats avg(*) as *

Tags (3)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

How about this?

your base search | eval quantity=round(quantity/12) | timechart sum(quantity) span=1h by sourcetype | stats max(*) as * 

Try this:

your base search | timechart sum(eval(quantity/12)) span=1h by sourcetype | stats max(*) as * 
| replace *.* with *#* | convert rmunit(*)

*Updated answer*
Don't know why I didn't think of this before. Try this

For max(*) as *

 your base search | bucket span=1h _time  | stats  sum(eval(quantity/12)) as total by _time, sourcetype | eval total=round(total) | eval temp=1| chart first(total) as total  over temp by sourcetype | fields - temp

For avg(*) as *

your base search | bucket span=1h _time  | stats  sum(eval(quantity/12)) as total by _time, sourcetype | stats avg(total) as total by sourcetype | eval total=round(total) | eval temp=1| chart first(total) as total  over temp by sourcetype | fields - temp

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

How about this?

your base search | eval quantity=round(quantity/12) | timechart sum(quantity) span=1h by sourcetype | stats max(*) as * 

Try this:

your base search | timechart sum(eval(quantity/12)) span=1h by sourcetype | stats max(*) as * 
| replace *.* with *#* | convert rmunit(*)

*Updated answer*
Don't know why I didn't think of this before. Try this

For max(*) as *

 your base search | bucket span=1h _time  | stats  sum(eval(quantity/12)) as total by _time, sourcetype | eval total=round(total) | eval temp=1| chart first(total) as total  over temp by sourcetype | fields - temp

For avg(*) as *

your base search | bucket span=1h _time  | stats  sum(eval(quantity/12)) as total by _time, sourcetype | stats avg(total) as total by sourcetype | eval total=round(total) | eval temp=1| chart first(total) as total  over temp by sourcetype | fields - temp

mmouse88
Path Finder

Excellent both worked. Thank you so much for your dedication

0 Karma

somesoni2
SplunkTrust
SplunkTrust

See the updated answer. I also updated the previous one to remove _time from final result.

0 Karma

mmouse88
Path Finder

awesome. it worked. Thank you. can i ask for the second part using avg(*) as *

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Got another alternative. Try the updated answer.

0 Karma

mmouse88
Path Finder

here are the values:
26 7.666636 7 6 5.583311 3.249987 2.999988 2

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you paste some of the result which it was not able to truncate?

0 Karma

mmouse88
Path Finder

thx. It was able to convert or truncate some values after the decimal. Only 5 values (column 1, 4, 5, 11) had no decimal.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer. Note that its not doing the rounding, its just truncating anything after the decimal point.

0 Karma

mmouse88
Path Finder

no worries, my fault. i might not of explain it clearly. without the round my query produce the results #.###### (6 decimal places)

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Sorry I got confused. Are the values for field quantity less than 6??

0 Karma

mmouse88
Path Finder

precision, do u mean to remove the divide by 12?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

If you want no decimal places then you don't have to specify the precision (see the syntax in my answer).

0 Karma

mmouse88
Path Finder

if i add to your command: eval quantity=round(quantity/12,1). It gives me one decimal place which is pretty close to what I want. would like 0 decimal places. replace ,1 with 0 then results is all 0s.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you post your current working query? one with no rounding?

0 Karma

mmouse88
Path Finder

sorry, it didn't work. results produce all 0s

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