SQL query to report on Asset Groups

spritchard · July 19, 2023, 2:48pm

Hello,

I’m completely new to SQL. Would someone be able to help me we writing a SQL query so I can report on the following:

For each Asset Group in a list specified in the SQL query itself, return count of unique devices and a summary count of vulnerabilities based on CVSS score severity (Critical, High, Medium, Low, None)

Thank you in advance for any help!

Steve

john_hartman · July 19, 2023, 4:35pm

github.com

rapid7/insightvm-sql-queries/blob/master/sql-query-export/Asset-Group-Metrics.sql

-- collects details on Asset Group based on critical, High, medium and low risk vulnd
-- Copy the SQL query below
WITH group_asset_summary as (
  SELECT
    dag.asset_group_id,
    dag.name,
    fa.asset_id
  FROM
    fact_asset as fa
    JOIN dim_asset da USING (asset_id)
    JOIN dim_asset_group_asset USING (asset_id)
    JOIN dim_asset_group dag USING (asset_group_id)
),
vulnerability_summary as (
  SELECT
    gas.asset_group_id,
    gas.name,
    gas.asset_id,
    dv.vulnerability_id,
    dv.cvss_score,

This file has been truncated. show original

This might be a good place to start