Custom SQL Report not filtering on TAGS

InsightVM
1

I’m new to Rapid 7 and custom reports so this may be my lack of experience.

I need to pull assets with vulnerabilities that have been on the assets > xx days which have specific custom tags. This is so we can priorities vulnerabilities that regular patch management is not picking up.

I have the following query which pulls data into a csv.
I also have a custom tag which identified certain groups of assets.
I am not filtering for the custom tag in the SQL but relying on the Scope interface to do the filtering. This is so we can use the same query but run it against different tags.

I use this query to pull the data, and then in the Scope interface I select the TAGS that I want to filter for in my output. The issue is that the TAG selection in the scope seems to make no difference to the exported csv results. It seems the Scope filter on the interface has no effect on the export.

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, fava.age_in_days, dos.description, dos.family
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)
ORDER BY da.ip_address ASC, dv.title ASC

The query is pulling ALL the assets as expected, but the CSV output also contains ALL the assets and isn’t being filtered by the custom TAG specified in the Scope interface.

I’ve also tried filtering the scope by Site, and that seems to have no effect either. It’s if the Scope in the interface where you can select, site, tages, group etc has no effect on the exported results from the query.

2

Hi Paul,

This isn’t going to be much help, but it might be worth contacting R7 support about this - something sounds wrong to me.

I’ve used the scope selections in custom reports for the last couple of years and they’ve always worked as expected, although I admittedly don’t remember ever using tags to filter but have used sites, asset groups etc. As a test I’ve taken your SQL query and used one of our tags for the scope and it’s filtered it based on the tag as expected.

3

Thanks for looking.

This is really strange to be honest and I’m not getting consistent results.

If I do a ‘SITE’ filter in the UI the number of records reduces but more than one site is in the output, or put a SITE filter in the SQL itself then only records for that site appear.

Example:
Using this sql but specifying a WHERE for the site name:

SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity, 
   round(dv.cvss_v3_score::numeric, 2) AS cvss_v3_score, round(dv.riskscore::numeric, 0) AS risk, dv.description as vulnerability, fava.age_in_days, dos.description AS OS, dos.family
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 fava.age_in_days>=60 and ds.name='name goes here'
ORDER BY fava.age_in_days DESC, dv.title ASC

I get 8722 rows in the output.

If I remove the da.name filter in the sql and instead select the exact same site name in the UI filter I get 10847 rows, which also includes other sites.

What I have now noticed though is my two sites are sometimes picking up the same asset. If I use the first sql then the results are just for that site. If I use the second sql then the number of rows is greater, but there are duplicates where the asset is being picked up by both sites names.

I think the UI filter isn’t being applied as strictly as the sql filter.

4

One other thing I’ve just thought of (and it’s something I still miss sometimes) is that after you select the site, tag, asset group etc in the scope you then have to scroll down to the bottom and there is a Done button. The selections are not saved until that button is clicked, so if you are selecting the tags and then just closing the window that could explain the problem.