# ES SQL Query in Canvas Returning Unexpected Value

**URL:** <https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [July 16, 2019, 10:53pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841 "2019-07-16T22:53:57Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![thegrockq](https://avatars.discourse-cdn.com/v4/letter/t/9fc348/32.png) [@thegrockq](https://discuss.elastic.co/u/thegrockq)\
**Post date:** [July 16, 2019, 10:53pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/1 "2019-07-16T22:53:57Z")

</div>

I'm attempting to calculate the sum of disk space used by specific log types and present them in a metric element in Canvas. For example, Winlogbeat: 3.8 TB, DNS: 2.8 TB, etc.

I've written a SQL query that returns the expected result in dev tools:

> POST \_xpack/sql  
> {  
> "query":"SELECT ROUND(SUM(index\_stats.total.store.size\_in\_bytes)/1073741824) AS storage FROM ".monitoring-es-6\*" WHERE index\_stats.index LIKE '%winlogbeat%' AND type = 'index\_stats' AND timestamp \> NOW() - INTERVAL 9 SECOND AND timestamp \< NOW()"  
> }

This returns the result I expect:

> {"columns":[{"name":"storage","type":"long"}],"rows":[[2896.0]]}

I'm trying to get this number to display in a metric element in Canvas. If I choose "Elasticsearch SQL" as my data source and use the exact same query (below), and choose to display the value of "storage," Canvas displays a zero.

> SELECT ROUND(SUM(index\_stats.total.store.size\_in\_bytes)/1073741824) AS storage FROM ".monitoring-es-6\*" WHERE index\_stats.index LIKE '%winlogbeat%' AND type = 'index\_stats' AND timestamp \> NOW() - INTERVAL 9 SECOND AND timestamp \< NOW()

If I change the settings to display a count, it shows a one (so I believe a result was returned). I don't understand why I'm seeing a zero and not ~2896. Here's the expression being used for the purpose of reproducing:

```
filters
| essql 
  query="SELECT ROUND(SUM(index_stats.total.store.size_in_bytes)/1073741824) AS storage FROM \".monitoring-es-6*\" WHERE index_stats.index LIKE '%winlogbeat%' AND type = 'index_stats' AND timestamp > NOW() - INTERVAL 9 SECOND AND timestamp < NOW()"
| math "storage"
| metric "Used Disk Space" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render

```

Obviously I'm new to both ES SQL and Canvas, so entirely possible the issue is due to my own ignorance.

---

<div class="post-metadata">

**Author:** ![monfera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/monfera/32/26467_2.png) [@monfera](https://discuss.elastic.co/u/monfera)\
**Post date:** [July 18, 2019, 7:33pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/2 "2019-07-18T19:33:05Z")

</div>

What do you get if you do a `| render as=debug` right after the query?

---

<div class="post-metadata">

**Author:** ![thegrockq](https://avatars.discourse-cdn.com/v4/letter/t/9fc348/32.png) [@thegrockq](https://discuss.elastic.co/u/thegrockq)\
**Post date:** [July 18, 2019, 8:35pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/3 "2019-07-18T20:35:37Z")

</div>

With this expression:

> filters  
> | essql  
> query="SELECT ROUND(SUM(index\_stats.total.store.size\_in\_bytes)/1073741824) AS storage FROM ".monitoring-es-6\*" WHERE index\_stats.index LIKE '%winlogbeat%' AND type = 'index\_stats' AND timestamp \> NOW() - INTERVAL 9 SECOND AND timestamp \< NOW()"  
> | math "storage"  
> | metric "Used Disk Space"  
> metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}  
> labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}  
> | render as=debug

I get this output in the metric element:

> {  
> "metric": "0",  
> "label": "Used Disk Space",  
> "metricFont": {  
> "type": "style",  
> "spec": {  
> "fontFamily": "'Open Sans', Helvetica, Arial, sans-serif",  
> "fontWeight": "normal",  
> "fontStyle": "normal",  
> "textDecoration": "none",  
> "textAlign": "center",  
> "fontSize": "48px",  
> "lineHeight": "48px",  
> "color": "#000000"  
> },  
> "css": "font-family:'Open Sans', Helvetica, Arial, sans-serif;font-weight:normal;font-style:normal;text-decoration:none;text-align:center;font-size:48px;line-height:48px;color:#000000"  
> },  
> "labelFont": {  
> "type": "style",  
> "spec": {  
> "fontFamily": "'Open Sans', Helvetica, Arial, sans-serif",  
> "fontWeight": "normal",  
> "fontStyle": "normal",  
> "textDecoration": "none",  
> "textAlign": "center",  
> "fontSize": "14px",  
> "lineHeight": 1,  
> "color": "#000000"  
> },  
> "css": "font-family:'Open Sans', Helvetica, Arial, sans-serif;font-weight:normal;font-style:normal;text-decoration:none;text-align:center;font-size:14px;line-height:1;color:#000000"  
> }  
> }

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [July 18, 2019, 10:56pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/4 "2019-07-18T22:56:20Z")

</div>

@thegrockq Do you have any filter elements on your workpad? Filters are applied globally unless you're using filter groups to opt in/out of the filter elements within the workpad.

What's the output you get when you just run the datasource portion of the expression?

```auto
filters
| essql 
  query="SELECT ROUND(SUM(index_stats.total.store.size_in_bytes)/1073741824) AS storage FROM \".monitoring-es-6*\" WHERE index_stats.index LIKE '%winlogbeat%' AND type = 'index_stats' AND timestamp > NOW() - INTERVAL 9 SECOND AND timestamp < NOW()"

```

---

<div class="post-metadata">

**Author:** ![thegrockq](https://avatars.discourse-cdn.com/v4/letter/t/9fc348/32.png) [@thegrockq](https://discuss.elastic.co/u/thegrockq)\
**Post date:** [July 19, 2019, 5:54pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/5 "2019-07-19T17:54:14Z")

</div>

Hi @Catherine_Liu - I only have a date filter and I get the same result if I include the last year until now (I only have 2 months worth of data right now).

If I just run the datasource portion of the expression, I get:

> storage #  
> 0

Side note: the data source is just the monitoring index, so this should be reproducible if you swap another index name and probably divide by less than "1073741824" in the SELECT statement (depending on the size of your indices) -- if you're interested in reproducing.

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [July 19, 2019, 11:21pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/6 "2019-07-19T23:21:35Z")

</div>

Is your time filter filtering on the right field, i.e. `timestamp`? By default, it filters on the `@timestamp` field, and you need to set it to your date field in the sidebar. If the field you're filtering on doesn't exist in your datasource, it won't retrieve any data.

 ![55%20PM](https://us1.discourse-cdn.com/elastic/original/3X/d/2/d27bc0db5f228a9e56aa6b6ca11d0ea7e6a5826d.png)

---

<div class="post-metadata">

**Author:** ![thegrockq](https://avatars.discourse-cdn.com/v4/letter/t/9fc348/32.png) [@thegrockq](https://discuss.elastic.co/u/thegrockq)\
**Post date:** [July 24, 2019, 3:13pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/7 "2019-07-24T15:13:56Z")

</div>

Thank you @Catherine_Liu! Yes - it appears monitoring stores timestamp in "timestamp," not "@timestamp." Can I selectively apply time filters to specific elements, and not others? (Can you point me in the right direction?)

---

<div class="post-metadata">

**Author:** ![thegrockq](https://avatars.discourse-cdn.com/v4/letter/t/9fc348/32.png) [@thegrockq](https://discuss.elastic.co/u/thegrockq)\
**Post date:** [July 24, 2019, 5:03pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/8 "2019-07-24T17:03:40Z")

</div>

Actually, I see the answer - filter groups. Not available on my current version (6.7), but will look for it once we get to 7.2. Thanks again for your help.

---

<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 21, 2019, 5:03pm UTC](https://discuss.elastic.co/t/es-sql-query-in-canvas-returning-unexpected-value/190841/9 "2019-08-21T17:03:41Z")

</div>

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