# Vulnerability Age **URL:** \ **Category:** SQL Queries\ **Created:** [June 29, 2020, 5:53pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319 "2020-06-29T17:53:13Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [June 29, 2020, 5:53pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/1 "2020-06-29T17:53:13Z")
Hello All, 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) Thanks in advance, Jacob ---
**Author:** ![tyler\_schmidtke](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/tyler_schmidtke/32/226_2.png) [@tyler\_schmidtke](https://discuss.rapid7.com/u/tyler_schmidtke)\ **Post date:** [June 29, 2020, 9:14pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/2 "2020-06-29T21:14:40Z")
👋 Does this table contain the information that you’re looking for? [https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-facts#section-fact-asset-vulnerability-age](https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-facts#section-fact-asset-vulnerability-age) ---
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [June 30, 2020, 11:04am UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/3 "2020-06-30T11:04:55Z")
I have tried using the table fact\_asset\_vulnerability\_age but it does not seem exist within the datawarehouse schema. In the link below it says I can choose the function age(date, unit) which I can choose “days” but it will not let me choose “days” but the function choosing age works. [https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#fact\_asset\_vulnerability\_finding\_remediation.date](https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#fact_asset_vulnerability_finding_remediation.date) ---
**Author:** ![tyler\_schmidtke](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/tyler_schmidtke/32/226_2.png) [@tyler\_schmidtke](https://discuss.rapid7.com/u/tyler_schmidtke)\ **Post date:** [June 30, 2020, 5:32pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/4 "2020-06-30T17:32:03Z")
@jacob_horning 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](https://www.postgresql.org/docs/12/functions-datetime.html) to get the following example format: ```auto 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`. ```auto age(favf.date, 'days') ``` This will return a floating point value that could be converted to an integer if you want with the following: ```auto ROUND(age(favf.date, 'days'))::integer ``` There are some of other ways to accomplish this that are documented, but are a bit more verbose. ---
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [July 2, 2020, 4:47pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/5 "2020-07-02T16:47:06Z")
@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. ---
**Author:** ![tyler\_schmidtke](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/tyler_schmidtke/32/226_2.png) [@tyler\_schmidtke](https://discuss.rapid7.com/u/tyler_schmidtke)\ **Post date:** [July 2, 2020, 4:51pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/6 "2020-07-02T16:51:46Z")
You can use the `extract` function for that. The previously linked documentation has details on the syntax 🙂 ---
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [July 2, 2020, 6:13pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/8 "2020-07-02T18:13:50Z")
@tyler_schmidtke 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) ---
**Author:** ![tyler\_schmidtke](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/tyler_schmidtke/32/226_2.png) [@tyler\_schmidtke](https://discuss.rapid7.com/u/tyler_schmidtke)\ **Post date:** [July 2, 2020, 6:42pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/9 "2020-07-02T18:42:23Z")
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: ```auto concat(extract(YEARS from age(favf.date)), ' years ', extract(MONTHS from age(favf.date)), ' months ', extract(DAYS from age(favf.date)), ' days') AS age ``` ---
**Author:** ![matt\_wyen](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/matt_wyen/32/2749_2.png) [@matt\_wyen](https://discuss.rapid7.com/u/matt_wyen)\ **Post date:** [July 10, 2020, 2:03pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/10 "2020-07-10T14:03:36Z")
be careful with SELECT DISTINCT 🙂 > r7\_aging AS ( > SELECT asset\_id > ,vulnerability\_id > ,first\_discovered > ,most\_recently\_discovered > FROM fact\_asset\_vulnerability\_age > ) ---
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [July 10, 2020, 2:08pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/11 "2020-07-10T14:08:38Z")
@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 ---
**Author:** ![matt\_wyen](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/matt_wyen/32/2749_2.png) [@matt\_wyen](https://discuss.rapid7.com/u/matt_wyen)\ **Post date:** [July 10, 2020, 2:23pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/12 "2020-07-10T14:23:59Z")
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. [https://stackoverflow.com/questions/33651429/should-i-use-distinct-in-my-queries](https://stackoverflow.com/questions/33651429/should-i-use-distinct-in-my-queries) ---
**Author:** ![jacob\_horning](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/919ad9/32.png) [@jacob\_horning](https://discuss.rapid7.com/u/jacob_horning)\ **Post date:** [July 10, 2020, 2:36pm UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/13 "2020-07-10T14:36:04Z")
@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. ---
**Author:** ![akhade](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/a/bc8723/32.png) [@akhade](https://discuss.rapid7.com/u/akhade)\ **Post date:** [March 30, 2022, 8:11am UTC](https://discuss.rapid7.com/t/vulnerability-age/1319/14 "2022-03-30T08:11:48Z")
@jacob_horning @matt_wyen Are you removing the instances manually? Because it will take a long time for huge data. Is there any alternate way to do it?