SQL Export - Including UUID and Limiting Solution

InsightVM SQL Queries
1

Hello, I am using this code for reports. I’d like to include the UUID in this. Additionally, when I do exports, the Solution has the solution listed multiple times in the same Cell. Does anyone know why that’d be happening?

WITH asset_vulns AS (
SELECT favi.asset_id, favi.vulnerability_id, string_agg(htmlToText(favi.proof), E’\n’) AS proof
FROM fact_asset_vulnerability_instance favi
GROUP BY favi.asset_id, favi.vulnerability_id
),
solutions AS (
SELECT av.vulnerability_id, string_agg(htmlToText(ds.fix), E’\n’) as fix
FROM asset_vulns av
JOIN dim_asset_vulnerability_best_solution davbs ON (davbs.asset_id = av.asset_id AND davbs.vulnerability_id = av.vulnerability_id)
JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
GROUP BY av.vulnerability_id
)
SELECT da.ip_address, da.host_name, dv.severity, dv.title, dv.date_published, av.proof, s.fix AS solution
FROM asset_vulns av
JOIN solutions s ON av.vulnerability_id = s.vulnerability_id
JOIN dim_asset da ON da.asset_id = av.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = av.vulnerability_id
ORDER BY da.ip_address ASC, da.host_name ASC

2

@ bradpjaxx You helped me before with this code. Would you be able to look at this? Thank you, and no worries if you don’t have time!

3

Is this from the Warehouse or is this On Prem Postgress for NeXpose ?

4

I think this is from NeXpose..

Added vulnerability_id AS uuid to SELECT
Grouped both asset_vulns and solutions by asset_id, vulnerability_id
Used DISTINCT inside string_agg() to avoid duplicate fix lines

Try this … it takes much longer to run… pulling that..

WITH asset_vulns AS (
    SELECT
        favi.asset_id,
        favi.vulnerability_id,
        string_agg(htmlToText(favi.proof), E'\n') AS proof
    FROM fact_asset_vulnerability_instance favi
    GROUP BY favi.asset_id, favi.vulnerability_id
),

solutions AS (
    SELECT
        davbs.asset_id,
        davbs.vulnerability_id,
        string_agg(DISTINCT htmlToText(ds.fix), E'\n') AS fix
    FROM dim_asset_vulnerability_best_solution davbs
    JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
    GROUP BY davbs.asset_id, davbs.vulnerability_id
)

SELECT
    da.ip_address,
    da.host_name,
    dv.vulnerability_id AS uuid,
    dv.severity,
    dv.title,
    dv.date_published,
    av.proof,
    s.fix AS solution
FROM asset_vulns av
JOIN solutions s
    ON av.asset_id = s.asset_id AND av.vulnerability_id = s.vulnerability_id
JOIN dim_asset da ON da.asset_id = av.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = av.vulnerability_id
ORDER BY da.ip_address ASC, da.host_name ASC;
5

Thank you! This fixed the Solution issue!

As for the UUID, I think what I’m looking for is the “dim_asset_unique_id” or “unique_id”
I’ve tried plugging this in a few different ways based on other codes I’ve seen online, but I’m not great with writing SQL.

6

are yuou looking for the SYSTEMS actuall UUID?

7

Yes, they’re unique Identifier. We use multiple tools and that is the one data I can trust to match them.

8

i dont think the OS UUID is captured in the on PREM, i dont think ive seen it, I knows its available in the WAREHOUSE…

ill do some looking around in the onprem stuff some more..

9

Found it.. give me a few min…

10

ok modified the existing from the top…

try this, i think this is what you are wanting..

WITH asset_vulns AS (
    SELECT
        favi.asset_id,
        favi.vulnerability_id,
        string_agg(htmlToText(favi.proof), E'\n') AS proof
    FROM fact_asset_vulnerability_instance favi
    GROUP BY favi.asset_id, favi.vulnerability_id
),

solutions AS (
    SELECT
        davbs.asset_id,
        davbs.vulnerability_id,
        string_agg(DISTINCT htmlToText(ds.fix), E'\n') AS fix
    FROM dim_asset_vulnerability_best_solution davbs
    JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
    GROUP BY davbs.asset_id, davbs.vulnerability_id
)

SELECT
    da.ip_address,
    da.host_name,
    daui.unique_id AS "Asset UUID",
    daui.source AS "UUID Source",
    dv.vulnerability_id AS uuid,
    dv.severity,
    dv.title,
    dv.date_published,
    av.proof,
    s.fix AS solution
FROM asset_vulns av
JOIN solutions s
    ON av.asset_id = s.asset_id AND av.vulnerability_id = s.vulnerability_id
JOIN dim_asset da ON da.asset_id = av.asset_id
LEFT JOIN dim_asset_unique_id daui ON da.asset_id = daui.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = av.vulnerability_id
ORDER BY da.ip_address ASC, da.host_name ASC;