Hi,
I feel bad for asking this, but I am having trouble in creating a very simple time series chart for a list of vulnerabilities.
I’m not sure what I’m doing wrong, so I’ll try to explain the problem in detail and what I’m trying to achieve.
The data comes from a relational db and more or less it would look something like this:
The idea would be to make a time series chart where all the “severity counts” are stacked together.
In the end should look something like this:
or this:
Moreover, the severity values should be viewed in a time interval of 1 month. This means that if you consider the sample table I provided above, id 4,5,6,7 should be “stacked” together in one data point for “June”.
Any suggestion is appreciated.
Welcome,
Please provide the data in a usable format not an image?
id,title,link,severity,discovery_time
1,xss,localhost:8080.medium,2023-01-11 03:23:54-05
Please provide the full data set or as much as you can to give you proper answer. You need to provide this data because we do not have access to your database and we do not have an image to data converter…yet 
Hi there.
Here you can see the data:
id | title | link | severity | discovery_time
----+--------------+--------------------+----------+------------------------
1 | vuln1 | http://hello.com | medium | 2023-03-11 03:23:54-05
2 | XSS vuln | http://example.com | critical | 2023-04-11 04:23:54-04
3 | SQLi vuln | http://sqli.com | high | 2023-05-11 04:23:54-04
4 | top xss vuln | http://top.com | medium | 2023-06-11 04:23:54-04
5 | vuln1 | http://hello.com | medium | 2023-06-11 03:23:54-04
6 | vuln1 | http://hello.com | medium | 2023-06-11 03:25:54-04
7 | top xss vuln | http://hello.com | medium | 2023-06-11 03:22:54-04
I changed the data, as there were some domains that I could not show.
The data would be in the same format as you see in the table.
If you need it in a different format let me know.
1 Like
Few weeks ago I was testing Grafana on an elasticsearch datasource.
As far as I remember there is a method to group by month all the results there.
I specifically remember you could do it in the UI, but I cannot find it anymore here.
even simpler just use something like this with a bar chart that has format set to timeseries
with src
as
(
select 1 as id,'vuln1' as title,' http://hello.com ' as link,'medium' as severity,'2023-03-11 03:23:54' as discovery_time union all
select 2,'XSS vuln' ,' http://example.com ','critical','2023-04-11 04:23:54' union all
select 3,'SQLi vuln',' http://sqli.com ','high','2023-05-11 04:23:54' union all
select 4,'top xss vuln',' http://top.com ','medium','2023-06-11 04:23:54' union all
select 5,'vuln1',' http://hello.com ','medium','2023-06-11 03:23:54' union all
select 6,'vuln1',' http://hello.com ','medium','2023-06-11 03:25:54' union all
select 7,'top xss vuln ',' http://hello.com ','medium','2023-06-11 03:22:54'
)
select value, metric, time
from (
select cast(discovery_time as date) as time,
title metric,
case
when severity = 'critical' then 1
when severity = 'medium' then 2
when severity = 'high' then 3
end as value
from src
) a
