I created values for the average CPU, memory and swap memory usage and managed to get it in a column chart. I'd like to get the chart to display the min/max of each field (cpu, memory, swap) — not the min/max of all the fields by date.
Here is my query and what my chart currently looks like:
index=os (sourcetype=cpu cpu=all) OR (sourcetype=vmstat)
| search host=$server_name$
| eval Percent_CPU_Load = 100 - pctIdle
| eval date=strftime(_time,"%A")
| stats avg(Percent_CPU_Load) avg(memUsedPct) avg(swapUsedPct) by date
| rename avg(Percent_CPU_Load) AS "Avg CPU" avg(memUsedPct) as "Avg Memory" avg(swapUsedPct) AS "Avg Swap Memory"
| stats values by myvalues
| eval sort_field = case(date=="Monday",1, date=="Tuesday",2, date=="Wednesday",3, date=="Thursday",4, date=="Friday",5, date=="Saturday",6, date=="Sunday",7)
| sort sort_field
| fields - sort_field
Hey guys, I'm still stuck. I'd like the graphs to look the way they are, but have the min/max label be for each category (CPU, memory, swap) NOT the date. Right now, it's giving me the min/max for the date field only. Please help!
Try the code below, I took a few liberties based on my test environment, such as bin'ing the data at 1m intervals and averaging those values (in case any are sampled more than 1m at a time. You'll also need to add your additional sort logic if you still need this. If you visualize this as a bar graph and enable Trellis it looks something like:
index=os (sourcetype=cpu cpu=all) OR (sourcetype=vmstat)
| table _time,pctIdle,swapUsedPct,memUsedPct
| bin _time span=1m
| stats avg(pctIdle) as cpu, avg(memUsedPct) as mem, avg(swapUsedPct) as swap by _time
| eval values=mvappend(values,if(isnull(cpu),null,"cpu="+tostring(cpu)),if(isnull(swap),null,"swap="+tostring(swap)),if(isnull(mem),null,"mem="+tostring(mem)))
| fields - cpu,mem,swap
| mvexpand values
| rex field=values "(?P<stat>.*)=(?P<value>.*)"
| fields - values
| stats avg(value) as avg,max(value) as max,min(value) as min by stat
| eval caption=case(stat="cpu","CPU Usage",stat="mem","Memory Used",stat=swap,"Swap Used")
| fields stat,caption,avg,max,min
Hi, I don't see the min/max labels on your chart...also, I'm on Splunk 6.5 so we don't have Trellis available 😞
your line 7 doesn't make any sense, and line 6 renames should be done on line 5 at the same time as the avg are calculated.
Oh whoops, you're right. I forgot to remove line 7 from a previous search. Just moved up the rename to line 6 so it now looks like this - thanks @DalJeanis!
| stats avg(Percent_CPU_Load) AS "Avg CPU" avg(memUsedPct) as "Avg Memory" avg(swapUsedPct) AS "Avg Swap" by date
I removed that line (it was just leftover from a previous search), but I still have the same problem with my charts not displaying min/max of a column chart by the values generated with stats
- (not by date).
| streamstats max(Avg CPU), min(Avg CPU) as max_cpu, min_cpu
Go to source code and add this max_cpu, min_cpu and see if it works.
Hi there, I don't understand what you mean. Can you show me where your suggestion fits in my query?