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?