Splunk Search

Round result of timechart avg()

mxanareckless
Path Finder

I cannot figure out how to round the values presented on the timechart. My SPL:

index=$radio_token$ host=$dropdown_token2$ sourcetype=cpu
| eval cpuavg=round(cpu_load_percent, 2)
| timechart avg(cpuavg)

And these are the results: 

mxanareckless_0-1612993558619.png

How can I get that to appear as 1%, instead of that huge clunky number? 

Labels (4)
Tags (3)
0 Karma
1 Solution

bowesmana
Champion

 

@mxanareckless 

Round after the timechart, not before, like this

 

 

index=$radio_token$ host=$dropdown_token2$ sourcetype=cpu
| timechart avg(cpu_load_percent) as cpuavg
| eval cpuavg=round(cpuavg, 2)

 

 

View solution in original post

bowesmana
Champion

 

@mxanareckless 

Round after the timechart, not before, like this

 

 

index=$radio_token$ host=$dropdown_token2$ sourcetype=cpu
| timechart avg(cpu_load_percent) as cpuavg
| eval cpuavg=round(cpuavg, 2)

 

 

View solution in original post

mxanareckless
Path Finder

@bowesmana

That worked for | timechart avg(cpu_load_percent) as cpuavg
However, if I add by host to the end, the rounding no longer works.

index=index sourcetype=vmstat
| where like(host, "kdc%")
| timechart avg(memUsedGB) as avgmem by host
| eval avgmem=round(avgmem,2)

mxanareckless_0-1613159267155.png

0 Karma

bowesmana
Champion

@mxanareckless 

When you use a split by clause, the name of the fields generated are the names of the split and no longer the name you want to give it, so if you look at the statistics tab when you do

| timechart avg(memUsedGB) as avgmem 

you will get a column called avgmem, which you can easily round.

When you do a split by, e.g.

| timechart avg(memUsedGB) as avgmem by host

you will see the columns do not have anything to do with avgmem in their names. In this case, you can't just use the round() function any more. You will have to use the foreach statement, which will iterate through each field, like this

| timechart avg(memUsedGB) as avgmem by host
| foreach * [ eval <<FIELD>>=round(<<FIELD>>,2) ]

 What this is doing is for each field name matching *, it will then run the eval statement in the subsearch and the <<FIELD>> reference is the actual value of the field, so you are just rounding the fields without having to know their names.

foreach is a very powerful and is one of the commands you can use well if you use good naming conventions in your field names in your SPL.

https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchReference/Foreach

Hope this was useful.

mxanareckless
Path Finder

@bowesmana 

Thank you very much for your insightful reply. I think I understand the concept, but when I apply your suggestion, all of the fields returned are blank instead. I need the values in the bottom screenshot to be rounded to the nearest whole integer - the excessive decimals make it very difficult to read in reports.

spk-foreach1.PNGspk-foreach2.PNG

0 Karma

bowesmana
Champion

@mxanareckless 

Mmm, the only thing that may be an issue is your field names. Please change the foreach statement to 

| foreach * [ eval <<FIELD>>=round('<<FIELD>>') ]

i.e. wrap the second <<FIELD>> with single quotation marks.  When dealing with field names that contain non standard characters or start with numbers, you need to use single quotes.