Request: Remediated Instances - Data warehouse

InsightVM SQL Queries
1

Hello, Does anyone have a good workaround to track remediated instances? I have a SQL query based on fact_asset_vulnerability_instance and using a hash generation (MD5) to generate a unique ID for each instance.

    LOWER(
        MD5(
            da.asset_id || ':' ||
            CAST(COALESCE(favi.port, 0) AS text) || ':' || 
            COALESCE(vrefs.cve_reference, '') || ':' ||
            COALESCE(favi.key, '') || ':' ||
            htmlToText(favi.proof)
        )
    ) AS unique_id,

The problem I am running into is the lack of relationships in the fact_asset_vulnerability_remediation_date table. a record will only show up if all instances have been remediated and even then, only as 1 record regardless of # of instances.

Currently I am using a delta report in excel with a match formula to check old report with new report, but it is slow and tedious.
My initial idea is exploring the possibility of creating my own snapshot table based on my existing query, automatically update it weekly (report cadence), append any new findings and mark missing as “Closed”.

Would love to hear if anyone has done something similar, before I jump down that rabbit hole :slight_smile: