SQL Export - Port Needed

InsightVM SQL Queries
1

I have this code, and I want the proof to include the port it was found on. How do I add that to this?

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
WHERE dv.title LIKE ‘%TLS/SSL%’
ORDER BY da.host_name ASC, da.ip_address ASC

1 Like
2

To include the port number where the vulnerability was found in the proof, you need to modify the WITH asset_vulns AS Common Table Expression (CTE) to also retrieve the port number from fact_asset_vulnerability_instance (favi).

Try this…

WITH asset_vulns AS (
SELECT
favi.asset_id,
favi.vulnerability_id,
favi.port, – Include the port number
string_agg(‘Port: ’ || favi.port || E’\nProof: ’ || htmlToText(favi.proof), E’\n\n’) AS proof
FROM fact_asset_vulnerability_instance favi
GROUP BY favi.asset_id, favi.vulnerability_id, favi.port – Ensure port is grouped properly
),
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, – Now includes Port information
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
WHERE dv.title LIKE ‘%TLS/SSL%’
ORDER BY da.host_name ASC, da.ip_address ASC;

3

Thank you for the reply.

I’m getting errors from this section of the string. It doesn’t seem to like the : or \ symbols here.

4

try this… fixed the issue and this validates, may have been a copy paste issue, i put it the code view here now.

WITH asset_vulns AS (
    SELECT
        favi.asset_id,
        favi.vulnerability_id,
        favi.port,  -- Include the port number
        string_agg('Port: ' || favi.port || E'\nProof: ' || htmlToText(favi.proof), E'\n\n') AS proof
    FROM fact_asset_vulnerability_instance favi
    GROUP BY favi.asset_id, favi.vulnerability_id, favi.port  -- Ensure port is grouped properly
),
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,  -- Now includes Port information
    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
WHERE dv.title LIKE '%TLS/SSL%'
ORDER BY da.host_name ASC, da.ip_address ASC;
5

This did validate and worked to export. The proof column now shows the Port #, but unfortunately, no longer shows the Proof. I may need to split up the findings into different columns.

6

let me check it and see what i can come up with …

7

see if this works, Use COALESCE(htmlToText(favi.proof), ‘[No Proof Available]’) to handle NULLs

WITH asset_vulns AS (
    SELECT
        favi.asset_id,
        favi.vulnerability_id,
        favi.port,  -- Include the port number
        string_agg(
            'Port: ' || favi.port || E'\nProof: ' || 
            COALESCE(htmlToText(favi.proof), '[No Proof Available]'), 
            E'\n\n'
        ) AS proof
    FROM fact_asset_vulnerability_instance favi
    GROUP BY favi.asset_id, favi.vulnerability_id, favi.port  -- Ensure port is grouped properly
),
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,  -- Now correctly includes Port and Proof information
    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
WHERE dv.title LIKE '%TLS/SSL%'
ORDER BY da.host_name ASC, da.ip_address ASC;
8

Got it working! You rock! Thank you for your help

1 Like