topic Re: How do I get min/max of a column chart PER field? in Splunk Search

How do I get min/max of a column chart PER field?

josephinemho — Fri, 21 Sep 2018 16:45:27 GMT

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

Re: How do I get min/max of a column chart PER field?

sandeepmakkena — Tue, 29 Sep 2020 21:21:22 GMT

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

Re: How do I get min/max of a column chart PER field?

DalJeanis — Fri, 21 Sep 2018 18:39:13 GMT

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.

Re: How do I get min/max of a column chart PER field?

josephinemho — Fri, 21 Sep 2018 22:18:00 GMT

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

Re: How do I get min/max of a column chart PER field?

grawlinsonperso — Sat, 22 Sep 2018 00:16:26 GMT

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

Re: How do I get min/max of a column chart PER field?

josephinemho — Thu, 04 Oct 2018 22:56:23 GMT

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!

Re: How do I get min/max of a column chart PER field?

josephinemho — Thu, 04 Oct 2018 22:57:43 GMT

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

Re: How do I get min/max of a column chart PER field?

josephinemho — Thu, 04 Oct 2018 22:58:22 GMT

Hi there, I don't understand what you mean. Can you show me where your suggestion fits in my query?

Re: How do I get min/max of a column chart PER field?

josephinemho — Thu, 04 Oct 2018 22:59:30 GMT

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 😞