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:
Results on Lens are showing around 5k vulnerabilities
Is there an error on my part, or is there another explanation?
Thanks.