So I am trying to produce how many days the a single vulnerability has been on a host. There is an option to produce this number in “Days” with the timestamp from dim_asset_vulnerability_finding. I am unsure how to implement this and get Vulnerability Age back as a column. This is the current query I am working on.
"SELECT DISTINCT asset_id, dv.title, favf.date From dim_asset da JOIN dim_asset_tag USING (asset_id) JOIN dim_tag dt USING (tag_id) JOIN dim_asset_service_configuration USING (asset_id) JOIN fact_asset_vulnerability_finding favf USING (asset_id) JOIN dim_vulnerability dv USING (vulnerability_id)
My apologies - for the data warehouse, you can use some Postgres functions to get the format that you’re looking for.
If you have the date that vulnerability was found, favf.date, you can use the age(timestamp) function to get the following example format:
1 years 7 mons 29 days 23 hours 29 mins 3.564 secs
To get the days specifically from this, you could use the following. Note, this syntax is seemingly undocumented, but it will accept the following intervals years, months, days, hours, minutes, seconds.
age(favf.date, 'days')
This will return a floating point value that could be converted to an integer if you want with the following:
ROUND(age(favf.date, 'days'))::integer
There are some of other ways to accomplish this that are documented, but are a bit more verbose.
@tyler_schmidtke Thank you for the help. One more question, what if I wanted to print out years, months, and days but not hours, minutes and seconds. Thank you.
Thank you so much for the reference. It seems with everything I found I can only select one field from the extract function. I am trying to extract multiple fields not sure if this is possible with this function or my syntax is off with this.
EXTRACT(‘months’ , ‘days’ , ‘years’, from favf.date)
You should be able to use CONCAT along with multiple calls of the age function, something like this might get the results that you’re looking for:
concat(extract(YEARS from age(favf.date)), ' years ', extract(MONTHS from age(favf.date)), ' months ', extract(DAYS from age(favf.date)), ' days') AS age
@matt_wyen is there a reason with DISTINCT not working because I use it in almost everyone one of my queries and it removes duplicates I noticed. We had a lot of issues with this and had to remove the duplicates via a python script. Thanks
Stack exchange is really better at explaining this than I am - its a “WHAT IS MY USE CASE” situation
Whoever told you using DISTINCT is a bad sign in itself is wrong. In reality, it all depends on what problem you are trying to solve by using DISTINCT in the first place.
If you’re querying a table that is expected to have repeated values of some field or combination of fields, and you’re reporting a list of the values or combinations of values (and not performing any aggregations on them), then DISTINCT is the most sensible thing to use. It doesn’t really make sense in my mind to use GROUP BY instead just because somebody thinks DISTINCT shouldn’t be used. Indeed, I think this is the kind of thing DISTINCT is designed for.
If OTOH you’ve found that your query has a bug meaning that repeated values are being returned, you shouldn’t use either DISTINCT or GROUP BY to cancel out this bug. Rather, you should figure out the cause of the bug and fix it.
Using DISTINCT as a safety net is also a poor practice, as it potentially hides problems, and furthermore it can be computationally expensive (typically O( n log n ) or O( n 2)). In this scenario, I can’t see that using GROUP BY instead would help you.
@matt_wyen that makes sense. What rapid7 does is thrown in how many instances a single vulnerability is found and include the port they found it on but the remediation would be the same for all of these instances. So to decrease the size of our reports we removed these instances and just kept one of them. The numbers then would be more closely related the numbers found in our dashboard cards.
