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
Revered Legend

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
Revered Legend

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

mmouse88
Path Finder

Excellent both worked. Thank you so much for your dedication

0 Karma

somesoni2
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.