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

**URL:** <https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [July 8, 2019, 6:13am UTC](https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290 "2019-07-08T06:13:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TsuWeiQuan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tsuweiquan/32/46252_2.png) [@TsuWeiQuan](https://discuss.elastic.co/u/TsuWeiQuan)\
**Post date:** [July 8, 2019, 6:13am UTC](https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290/1 "2019-07-08T06:13:44Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/d/8dbbeb55f9e1f2a83e4014e41535e19b2da46e1b.png)

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?

---

<div class="post-metadata">

**Author:** ![joshdover](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joshdover/32/42020_2.png) [@joshdover](https://discuss.elastic.co/u/joshdover)\
**Post date:** [July 8, 2019, 3:23pm UTC](https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290/2 "2019-07-08T15:23:37Z")

</div>

I'd recommend using the [`HISTOGRAM` SQL function](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-grouping.html) to create time buckets and using the `AVG` function on each column.

Your query should look something like:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![TsuWeiQuan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tsuweiquan/32/46252_2.png) [@TsuWeiQuan](https://discuss.elastic.co/u/TsuWeiQuan)\
**Post date:** [July 9, 2019, 1:03am UTC](https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290/3 "2019-07-09T01:03:58Z")

</div>

Thanks, that works like a charm 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 6, 2019, 1:04am UTC](https://discuss.elastic.co/t/canvas-how-to-show-average-hourly-data-only-on-x-axis-from-last-24-hours/189290/4 "2019-08-06T01:04:02Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.