Hello Team,
I hope you are all doing well!!
I am looking for an option to add a vulnerability age filter while creating a report. For example, I want to create a report with the below-mentioned filters:
CVSS score > 7
Vulnerability age > 90 days
Appreciated your help and thanks in advance.
Thanks,
Parth Khundiwala
vulnerability.cvssScore > 7 && vulnerability.datePublished < /NOW - P90D/
I donât see âvulnerability.datePublished < /NOW - P90D/â option while generating report.
This you should be run in Query builder not under reports section.
vulnerability.cvssScore > 7 && vulnerability.datePublished < /NOW - P90D/
Thatâs correct. I am getting these options in the query builder. But I need this option in the report section as I need to pull the report with this filter and eventually we will reduce the days from 90 to 60 and 30.
The simplest way is a custom SQL report. I have included a step-by-step for you to learn how to do this yourself.
Step by step:
? next to define a SQL queryVulnerability Findings
order by
where dv.date_published > current_date - interval '90 days'
TIP: when testing SQL, sometimes it can take a while. To save time. Scope The report to a small site, asset group, or a small number of assets before doing a preview or running the query.
When you get done it should look like this
SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
where dv.date_published < current_date - interval '90 days'
ORDER BY da.ip_address ASC, dv.title ASC
If you want to learn more SQL reporting, I would recommend messing around with the sample reports that are included in step 7 above, then looking up the tables below on the web links. Try and add or remove a field for one of the tables that are included. Next thing you know you will be making your own from scratch. If you get stuck, google try your best. post what you are trying here, it is easier to help someone if they share what they are struggling with.
Take a look at
https://docs.rapid7.com/nexpose/creating-reports-based-on-sql-queries
https://docs.rapid7.com/nexpose/understanding-the-reporting-data-model-dimensions
https://docs.rapid7.com/nexpose/understanding-the-reporting-data-model-facts
Hi @troy_newcomb,
Thanks for the detailed explanation and for sharing the final query. I am trying to edit the query and add a condition where the CVSS score is greater than equals 7. However, facing some challenges (that is because of not having hands-on on SQL queries).
@pkhundiwala
You can add this to the where
and dv.cvss_score >= 7
SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
where dv.date_published < current_date - interval '90 days' and dv.cvss_score >= 7
ORDER BY da.ip_address ASC, dv.title ASC
To further narrow down the results, I have added âdos.description LIKE âMICROSOFTââ to the query, and the final query is as below. However, not getting any output.
SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity, dos.description AS operating_system,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
where dv.date_published < current_date - interval â90 daysâ and dv.cvss_score >= 7 and dos.description LIKE âMICROSOFTâ
ORDER BY da.ip_address ASC, dv.title ASC
Ok. I did multiple trials and errors and was able to get the right query. Below is the one.
SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity, dos.description AS operating_system,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
where dv.date_published > current_date - interval â90 daysâ and dv.cvss_score >= 7 and dos.description LIKE â%Microsoft Windows%â is true
ORDER BY da.ip_address ASC, dv.title ASC
Next, I am trying to get the âVulnerability age in dayâ column in the report.
Nice job getting like to work. like is case sensitive as I can see you worked around that issue.
Postgres has a function just like like that does a case insensitive search, it is called ilike.
See the documentation page here. PostgreSQL: Documentation: 11: 9.7. Pattern Matching
SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity, dos.description AS operating_system,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk, round (age_in_days::numeric, 0) as age_in_days
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
join fact_asset_vulnerability_age fava using (asset_id, vulnerability_id)
where dv.date_published > current_date - interval '90 days' and dv.cvss_score >= 7 and dos.description LIKE '%Microsoft Windows%' is true
ORDER BY da.ip_address ASC, dv.title ASC
Hi @Troy_newcomb,
When I validate the query, the query seems to be working fine however I am unable to pull the report. When I try to run the report is getting aborted. I am trying to pull the report for a custom tag which has around 187 assets.
Any idea what could be the reason? is it because of server resources?
Thanks,
Parth
You should share your query and put in preformatted text looks like </>
Try a preview after you validate the SQL.
Reduce the scope of the report to one asset that you know should have vulnerabilities. Maybe try on a small site or another tab.
It could be resources. If you want to share your show host output from https://docs.rapid7.com/nexpose/using-the-command-console/. That may help understand the resources. I would mask out the name and IP when posting in public
.
It is possible that it is resources.
Previewing and validation of the SQL query work fine. Yes, it does take time to get the results when we preview, but it gives the results.
I believe you are interested in seeing the below out for the âshow hostâ command.
Looks like you have disk space.
Try to start with a fresh report and try again.