Elastic Canvas: Discrepancy on data using ES SQL


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:

Results on Lens are showing around 5k vulnerabilities

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


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