Example name of group is “Global Assets”. Looking to grab Asset Name, IP, CVSS, Exploit count, Vuln title, Vuln Proof, Site, Date last scanned, First found, Vuln age
Hi,
The best way to do this is to check here for a similar query to the one you want and then edit it:(insightvm-sql-queries/sql-query-export at master · rapid7/insightvm-sql-queries · GitHub)
Check these links for more details on the data structure. The schema is for the full data warehouse but it’s usually close enough to use with the non-warehouse database
Data Model: Understanding the reporting data model: Dimensions | Nexpose Documentation
Schema - Dimensional Data Warehouse Schema
Finally, to run it for a specific asset group you can pick that asset group in the report setup page - there’s no need to build that into the query.
Vuln Age is a Specific Item tied to a specific VULN not on a single asset, your request isnt possible to use that.
If you leave that out you can certainly do that.
You will need to join quite a bit and it would be a one line item, here is one that i use to pull from the console, just select the asset group you want
WITH asset_owners AS (
SELECT DISTINCT asset_id, array_to_string(array_agg(dt.tag_name), ‘,’) AS owners
FROM dim_tag_asset dta
JOIN dim_tag dt USING (tag_id)
WHERE tag_type=‘OWNER’
GROUP BY asset_id
),
asset_locations AS (
SELECT DISTINCT asset_id, array_to_string(array_agg(dt.tag_name), ‘,’) AS locations
FROM dim_tag_asset dta
JOIN dim_tag dt USING (tag_id)
WHERE tag_type=‘LOCATION’
GROUP BY asset_id
),
asset_custom AS (
SELECT DISTINCT asset_id, array_to_string(array_agg(dt.tag_name), ‘,’) AS custom
FROM dim_tag_asset dta
JOIN dim_tag dt USING (tag_id)
WHERE tag_type=‘CUSTOM’
GROUP BY asset_id
),
asset_sites AS (
SELECT asset_id, array_to_string(array_agg(ds.name), ‘,’) AS sites
FROM dim_site_asset dsa
JOIN dim_site ds USING(site_id)
GROUP BY asset_id
),
asset_OS AS (
SELECT asset_id, dos.description AS OS, MAX(daos.certainty) AS certainty
FROM dim_asset_operating_system daos
JOIN dim_operating_system dos USING(operating_system_id)
GROUP BY asset_id, description
),
credential_status AS (
SELECT asset_id, aggregated_credential_status_description
FROM fact_asset
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
GROUP BY asset_id, aggregated_credential_status_description
)
SELECT da.ip_address AS “Address”, da.host_name AS “Name”, asi.sites AS “Site”, aos.OS AS “Operating System”,
fa.exploits AS “Exploits”, fa.malware_kits AS “Malware”, fa.moderate_vulnerabilities AS “Moderate”,
fa.severe_vulnerabilities AS “Severe”, fa.critical_vulnerabilities AS “Critical”, fa.vulnerabilities AS “Vulnerabilities”,
al.locations AS “Tag [Location]”, ao.owners AS “Tag [Owner]”, aos.certainty AS “Certainty”,
cs.aggregated_credential_status_description AS “Credential Status”
FROM fact_asset fa
JOIN dim_asset da USING(asset_id)
LEFT JOIN asset_owners ao USING(asset_id)
LEFT JOIN asset_locations al USING(asset_id)
LEFT JOIN asset_custom ac USING(asset_id)
JOIN asset_sites asi USING(asset_id)
JOIN asset_OS aos USING(asset_id)
JOIN credential_status cs USING(asset_id)