Vulnerability Age

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

:wave:

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

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

@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 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.

You can use the extract function for that. The previously linked documentation has details on the syntax :slight_smile:

@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)

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