Search for specific CVE incuding location information

prasanna_m · January 6, 2023, 7:13am

I’d like to create a report with hostname, ip address & location (tag) information, kindly help with SQL query

john_hartman · January 6, 2023, 2:07pm

rapid7/insightvm-sql-queries/blob/master/sql-query-export/Assets-With-All-Tags.sql

-- Console query to list all assets by IP and Host Name with the corresponding tags for each tag category
-- Copy the SQL query below

WITH 
custom_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as custom_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'CUSTOM'
GROUP BY dta.asset_id
),
location_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as location_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
GROUP BY dta.asset_id
),
owner_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as owner_tags

This file has been truncated. show original

prasanna_m · January 7, 2023, 7:45am

thanks much @john_hartman
I missed to ask one more thing,

I’d like to create a report for specific CVE with hostname, ip address & location (tag) details in csv format, kindly help with SQL query

Thanks for your support

john_hartman · January 9, 2023, 1:45pm

So just like the query above, for any example queries the best place to start is the Github repo we have where that above query is in.

That repo has ~100 or so queries that may already have what you’re looking for with some minor edits that you might need to make to specifically show what it is you’re looking for.

prasanna_m · March 21, 2023, 4:17pm

Hi @john_hartman ,
I’ve gone through these articles but unabe to create a query for my requirement.
I need to run a query for specific CVE and generate a csv report to show hostnam, ip address, title solution,fix & location tag
Please help

github.com

rapid7/insightvm-sql-queries/blob/master/sql-query-export/Assets-By- Specific-CVE-Results.sql

-- Report of assets that have a specific list of CVE. You can specify the CVEs starting on line 11. Example CVEs listed below. 
-- Copy the SQL query below


SELECT da.host_name, da.ip_address, ds.nexpose_id, dv.title, ds.applies_to, ds.solution_type, ds.fix
FROM dim_solution ds
JOIN dim_vulnerability_solution dvs USING(solution_id)
JOIN dim_asset_vulnerability_solution davs USING(vulnerability_id)
JOIN dim_vulnerability dv USING(vulnerability_id)
JOIN dim_asset da USING(asset_id)
WHERE ds.nexpose_id ilike '%CVE-2018-20250%'
OR dv.title ilike '%CVE-2017-11882%'
OR dv.title ilike '%CVE-2017-11774%'

john_hartman · March 21, 2023, 4:40pm

SELECT
da.ip_address,
da.host_name,
dv.title,
ds.summary,
htmlToText(ds.fix),
dt.tag_name

FROM fact_asset_vulnerability_instance favi

JOIN dim_asset da ON favi.asset_id=da.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id=dv.vulnerability_id
JOIN dim_solution ds ON dv.nexpose_id=ds.nexpose_id
JOIN dim_tag_asset dta ON da.asset_id=dta.asset_id
JOIN dim_tag dt ON dta.tag_id=dt.tag_id


WHERE dt.tag_type = 'LOCATION'
AND  dv.nexpose_id LIKE '%Your CVE here'

ORDER BY da.ip_address DESC

prasanna_m · March 21, 2023, 5:50pm

Hi @john_hartman , its not giving any results though we’ve multiple assets impacted with CVE-2023-23397. I modified,

dv.nexpose_id LIKE ‘%CVE-2023-23397%’

Kindly help

john_hartman · March 22, 2023, 5:15pm

The only thing that could stop it would be if your tags are not actually created as LOCATION tags and were instead created as CUSTOM tags