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 table is appended with the same data.  Vulnerabilities found in the finding_date table that are not in the current finding table can be considered remediated. I created a unique_key column by combining the asset_id + vulnerability_id.  For each unique_key, the remediation_date would be the LASTDATE() + 1 for all unique_keys that are not found in the finding table.

 

What I would like to do is create a calculated table from these 2 tables with the following columns: asset_id, vulnerability_id, (or unique_key), first_found_date, vulnerability_instances, remediation_date (if available).  I can then add calculated columns for measuring SLA's and vulnerability status.

 

 

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

     

10 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    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

     

    • tvm_analyst's avatar
      tvm_analyst
      Frequent Visitor
      7 years ago

      Thank you for the response sturlaws !  However this would assign the min date found in the entire table to all the vulnerabilities.   I would like to do something like a GROUPBY(unique_key) and find the MIN/MAX value within each group... if that makes sense.

       

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        7 years ago

        tvm_analyst, since you have not made any sample data available, I don't know excatly how your data looks, so the code should be consider more as guidelines for you to continue working on to get your desired result