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