ES SQL Query in Canvas Returning Unexpected Value

[thegrockq]

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.

[monfera (Robert Monfera)]

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

[thegrockq]

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"
}
}

[Catherine_Liu]

@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?

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()"

[thegrockq]

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.

[Catherine_Liu]

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.

[thegrockq]

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?)

[thegrockq]

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.