# Elastic Canvas: Discrepancy on data using ES SQL

**URL:** <https://discuss.elastic.co/t/elastic-canvas-discrepancy-on-data-using-es-sql/350882>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [January 11, 2024, 6:35pm UTC](https://discuss.elastic.co/t/elastic-canvas-discrepancy-on-data-using-es-sql/350882 "2024-01-11T18:35:24Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pikaia](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@pikaia](https://discuss.elastic.co/u/pikaia)\
**Post date:** [January 11, 2024, 6:35pm UTC](https://discuss.elastic.co/t/elastic-canvas-discrepancy-on-data-using-es-sql/350882/1 "2024-01-11T18:35:24Z")

</div>

Hi,

I've been uploading vulnerabilities to Elastic where the fields are already mapped to ECS, and so far, everything has been working fine. Now, I have the need to generate a report at the beginning of each month to present, for example, the number of vulnerabilities by severity for the last month. I decided to use Canvas for this purpose.

Initially, I created a graph on Lens and added it directly to the report. However, I'm not satisfied with how it looks (time range at the top, etc). I opted to generate a chart element directly on Canvas and retrieve the data using an ES SQL query. Two issues have arisen from here.

Firstly, I attempted to use the following query, but it doesn't yield results when the current month is January. It seems to work fine for the remaining months:  
`SELECT vulnerability.severity FROM "vulnerabilities-*" where MONTH_OF_YEAR("@timestamp") = ((MONTH_OF_YEAR(TODAY())- 2) % 12) + 1`

Secondly, when I tried to hardcode the dates to get results, it seemed to work fine. However, upon examining the data, I noticed that not all the data is appearing when compared to Lens or even the documents in discovery. I used the query:  
`SELECT vulnerability.severity FROM "vulnerabilities-*" WHERE "@timestamp" > CAST('2023-12-01T00:00:01Z' AS TIMESTAMP) and "@timestamp" < CAST('2023-12-31T23:59:59Z' AS TIMESTAMP)`

Results on canvas:  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/6/c60473585e3951a781de7b26ce7023984ff87449.png)

Results on Lens are showing around 5k vulnerabilities

Is there an error on my part, or is there another explanation?

Thanks.

---

<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:** [February 8, 2024, 6:36pm UTC](https://discuss.elastic.co/t/elastic-canvas-discrepancy-on-data-using-es-sql/350882/2 "2024-02-08T18:36:12Z")

</div>

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