Forum Discussion

tvm_analyst's avatar
tvm_analyst
Frequent Visitor
7 years ago
Solved

Calculating remediated vulnerabilities

I have 2 fact tables containing vulnerability data, a finding table and a finding_date table.  The finding table is overwritten with the current findings after scans are run, the finding_date t...
Need Help
  • sturlaws's avatar
    sturlaws
    7 years ago

    hi tvm_analyst 

    you could try something like this:

    new table =
ADDCOLUMNS (
    SUMMARIZE (
        'finding_date',
        'finding_date'[asset_id],
        'finding_date'[vulnerability_id]
    ),
    "firstFoundDate", CALCULATE ( MIN ( 'finding_date'[date] ) ),
        "lastFoundDate", CALCULATE ( Max ( 'finding_date'[date] ) )
)

    then, assuming that asset_id and vulnerability_id is unique in the finding-table, create a new column in the new table

    isSolvedFlag =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'finding'[date],
            'finding'[asset_id], 'new table'[asset_id],
            'finding'[vulnerability_id], 'new table'[vulnerability_id]
        )
    ),
    1,
    0
)

    and then you should be on you way

     

sturlaws's avatar
sturlaws
Resident Rockstar
7 years ago

How about first creating a column in the finding-table to identify unique ids which are not present in finding_date-table:

DetectedFlag =
IF (
    COUNTROWS (
        FILTER (
            fact_asset_vulnerability_finding_date;
            fact_asset_vulnerability_finding_date[unique_key]
                = CALCULATE ( VALUES ( fact_asset_vulnerability_finding[unique_key] ) )
        )
    ) >= 1;
    0;
    1
)


then use this flag, together with a union query to add these rows to the Assets-table-query

Assetts = 
union(
ADDCOLUMNS(
    SUMMARIZE(
        fact_asset_vulnerability_finding_date;
        fact_asset_vulnerability_finding_date[asset_id];
        fact_asset_vulnerability_finding_date[vulnerability_id];
        fact_asset_vulnerability_finding_date[unique_key]
    );
    "First found date";CALCULATE(min(fact_asset_vulnerability_finding_date[first_found_date]));
    "Last date written";CALCULATE(max(fact_asset_vulnerability_finding_date[date_written])))
;
ADDCOLUMNS(    
    SUMMARIZE(
        FILTER(fact_asset_vulnerability_finding;fact_asset_vulnerability_finding[DetectedFlag]=1);
        fact_asset_vulnerability_finding[asset_id];
        fact_asset_vulnerability_finding[vulnerability_id];
        fact_asset_vulnerability_finding[unique_key]
    );
    "First found date";CALCULATE(min(fact_asset_vulnerability_finding[first_found_date]));
    "Last date written";BLANK()))

Alternatively, you can try to do this in power query during the import of the data

tvm_analyst's avatar
tvm_analyst
Frequent Visitor
7 years ago

Thank you very much for your help sturlaws !  I ended up adding a column to the finding_date table for a status of open/closed and using this for the summarized table.  Open items still get a remediation date, but they are jsut filtered out later.  Thanks again.

summarized_finding_date = 
		ADDCOLUMNS(
			SUMMARIZE(
				'public fact_asset_vulnerability_finding_date',
				'public fact_asset_vulnerability_finding_date'[asset_id],
                                'public dim_asset'[credential_status],
                                'public dim_asset'[host_name],
                                'public dim_vulnerability'[risk_score],
                                 tag_owner[public dim_tag.name],
				'public fact_asset_vulnerability_finding_date'[vulnerability_id],
				'public dim_vulnerability'[sla_target],
                                'public dim_vulnerability'[dlx_severity],
                                 dlx_cat_class[lookups.Classification],
                                 dlx_cat_parent[lookups.Parent Technology],
				'public fact_asset_vulnerability_finding_date'[open],
                                'public fact_asset_vulnerability_finding_date'[unique_key]),
			"first_found_date", CALCULATE(MIN('public fact_asset_vulnerability_finding_date'[day])),
			"remediation_date", CALCULATE(MAX('public fact_asset_vulnerability_finding_date'[day]) +1))