help on complex bar chart

[jip31]

hi
With the xml below, i display a complex bar chart that you can see in the screenshot
I would like to modify 3 things :
1 - I need to delete "Number" under the X axis
2 - Under each bar of the chart I would like to have the scale that is actually in the legend
Could you help me please?
3- I need to do an average on process_cpu_used_percent field
Something like :

 | eval cpu_range=case(avg(process_cpu_used_percent>0 AND process_cpu_used_percent <=20,"0-20",



 <row>
    <panel>
      <title>CPU overall usage</title>
      <chart>
        <search>
          <query> `CPU` 
| fields process_cpu_used_percent host 
| eval host=upper(host) 
| eval cpu_range=case(process_cpu_used_percent>0 AND process_cpu_used_percent <=20,"0-20",
    process_cpu_used_percent>20 AND process_cpu_used_percent <=40,"20-40",
    process_cpu_used_percent>40 AND process_cpu_used_percent <=60,"40-60",
    process_cpu_used_percent>60 AND process_cpu_used_percent <=80,"60-80",
    process_cpu_used_percent>80 AND process_cpu_used_percent <=100,"80-100") 
| chart dc(host) as "Number" by cpu_range 
| append 
    [| makeresults 
    | fields - _time 
    | eval cpu_range="0-20,20-40,40-60,60-80,80-100" 
    | makemv cpu_range delim="," 
    | mvexpand cpu_range 
    | eval "Number"=0] 
| dedup cpu_range 
| sort cpu_range 
| transpose header_field=cpu_range 
| search column!="_*" 
| rename column as cpu_range</query>
          <earliest>-7d@h</earliest>
          <latest>now</latest>
        </search>
        <option name="charting.axisTitleX.text">CPU Usage (%)</option>
        <option name="charting.axisTitleY.text">Number of hosts</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.maximumNumber">1000</option>
        <option name="charting.axisY.minimumNumber">0</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"0-20":0x49B849,"20-40":0x006EAA,"40-60":0xE0AC16,"60-80":0xDA742E,"80-100":0xC84535}</option>
        <option name="charting.legend.placement">right</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
  <row>

[niketn · Accepted solution]

@jip31 instead of transpose use eval{fieldname} expression after sort command in your current query. Try the following and confirm:

 `CPU` 
 | fields process_cpu_used_percent host 
 | eval host=upper(host) 
 | eval cpu_range=case(process_cpu_used_percent>0 AND process_cpu_used_percent <=20,"0-20",
     process_cpu_used_percent>20 AND process_cpu_used_percent <=40,"20-40",
     process_cpu_used_percent>40 AND process_cpu_used_percent <=60,"40-60",
     process_cpu_used_percent>60 AND process_cpu_used_percent <=80,"60-80",
     process_cpu_used_percent>80 AND process_cpu_used_percent <=100,"80-100") 
 | chart dc(host) as "Number" by cpu_range 
 | append 
     [| makeresults 
     | fields - _time 
     | eval cpu_range="0-20,20-40,40-60,60-80,80-100" 
     | makemv cpu_range delim="," 
     | mvexpand cpu_range 
     | eval "Number"=0] 
 | dedup cpu_range 
 | sort cpu_range 
 | fields cpu_range Number 
 | eval {cpu_range}=Number 
 | fields - Number

For the 3rd point 3- I need to do an average on process_cpu_used_percent field you need to perform average on process_cpu_used_percent by which field?

[jip31]

thanks for the 2 first point
the only thing is strange is that the X axis scale is not aligned in front of the bar chart. Is it normal?
Or do i have to play with the bar char options?
Concerning the last point, actually we count the number of host which have a process cpu used percent >0 and <20, >20 and <40.......
It means that for a specific host we collect all process and that we count the process cpu used percent between 0 and 20 or 20 and 40 ...
So I need that the count is based on the average of all process by host instead of all process by host
the field relaed to the process is "process_name"

[niketn]

@jip31, it is expected to be within the x-axis data label since the ticks on x-axis and y-axis is applied dynamically along with data labels. Since your x-axis labels are fixed you can definitely tweak the position of x-axis ticks/data label using CSS override (as per your actual panel width, monitor/browser resolution etc). However, I would suggest better leave it dynamic.

For the average part seems like your logic happens in the macro CPU, so for assisting you further we would need to look at the macro definition and possibly some raw sample data which is processed by the macro.

[to4kawa]

Sample:

| makeresults 
| fillnull a b c d e
| eval a=10, b=10,c=20,d=20,e=40,_time=substr(_time,1,4)
| rename _time as "Number Of Host", a as "0-20", b as "20-40", c as "40-60", d as "60-80", e as "80-100"
| table "Number Of Host" *

[jip31]

it doesnt help me much more...

[vnravikumar]

Can you please attach the screenshot?

[jip31]

Here is sorry