Canvas: How to show average hourly data only on x axis from last 24 hours?

[TsuWeiQuan]

How can i show the average hourly data from last 24 hours?

Do i handle this on the essql query side or should take a set of raw data from
elasticsearch and utilize the coded expression on canvas to show last few hourly data
or
should i do a group by function on my sql query to get the average?

Currently, i managed to output all table data to Canvas and it is displaying nicely; Except for the time on the x axis. As seen in the photo below.

This is my current settings:

filters
| essql 
  query="SELECT CAST(\"@timestamp\" AS DATETIME) + INTERVAL 8 HOURS AS time, system.cpu.user.pct as user, system.cpu.system.pct as system, system.cpu.nice.pct as nice, system.cpu.irq.pct as irq, system.cpu.softirq.pct as softirq, system.cpu.iowait.pct as iowait, system.cpu.cores as numCore FROM \"metricbeat-*\" where host.hostname='NUSSERVER' AND system.cpu.cores IS NOT NULL ORDER BY time DESC"
| alterColumn column=time type=date
| sort by=time
| mapColumn "time" fn={getCell column=time | formatdate format="DD MMMM hh:mm" }
| mapColumn "user"  fn={math "(user/numCore)*100.0"}
| mapColumn "system"  fn={math "(system/numCore)*100.0"}
| mapColumn "nice"  fn={math "(nice/numCore)*100.0"}
| mapColumn "irq"  fn={math "(irq/numCore)*100.0"}
| mapColumn "softirq"  fn={math "(softirq/numCore)*100.0"}
| mapColumn "iowait"  fn={math "(iowait/numCore)*100.0"}
| ply 
    by="time" 
    expression={csv 
     data={string 
       "cpu_type, cpu_process
       " "user," {getCell "user"} "
       " "system," {getCell "system"} "
       " "nice," {getCell "nice"} "
       " "irq," {getCell "irq"} "
       " "softirq," {getCell "softirq"} " 
       " "iowait," {getCell "iowait"}
      } 
    }
| alterColumn "cpu_process" type="number"
| pointseries x="time" y="cpu_process" color="cpu_type"
| plot 
    defaultStyle={seriesStyle lines=1 fill=1 stack=0} 
    palette={palette "#01A4A4" "#CC6666" "#D0D102" "#616161" "#00A1CB" "#32742C" "#F18D05" "#113F8C" "#61AE24" "#D70060" gradient=false}
| render

I hope to receive some advice to how i can fix this?

[joshdover]

I'd recommend using the HISTOGRAM SQL function to create time buckets and using the AVG function on each column.

Your query should look something like:

SELECT
  HISTOGRAM("@timestamp", INTERVAL 1 HOUR) as time,
  AVG(system.cpu.user.pct) as user,
  AVG(system.cpu.system.pct) as system,
  AVG(system.cpu.nice.pct) as nice,
  AVG(system.cpu.irq.pct) as irq,
  AVG(system.cpu.softirq.pct) as softirq,
  AVG(system.cpu.iowait.pct) as iowait,
  AVG(system.cpu.cores) as numCore
FROM "metricbeat-*"
WHERE host.hostname='NUSSERVER' AND system.cpu.cores IS NOT NULL
GROUP BY time
ORDER BY time DESC

[TsuWeiQuan]

Thanks, that works like a charm