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

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

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        7 years ago

        if you care to upload some sample data I will take a look at it :)