Forum Discussion

king2005r's avatar
king2005r
Helper III
5 years ago
Solved

Vulnerabilities status report

Hi I have a table with monthly vulnerabilities, Plugin ID, and Host IP, I need to do the below measurement  : 1- Get repeated monthly vulnerabilities 2- Get the count of the new vulnerabilities 3...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi king2005r ,

     

    Create 2 calculated columns as below:

    Repeated = 
 var _previous=CALCULATE(MAX('Table'[Plugin ID]),FILTER('Table','Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<EARLIER('Table'[Open Date])&&'Table'[Plugin ID]=EARLIER('Table'[Plugin ID])))
 Return
 IF(_previous=BLANK(),"Not repeated","Repeated")
    Count of Plugin vulnerability = 
var _count=CALCULATE(COUNT('Table'[Plugin ID]),FILTER('Table','Table'[Plugin ID]=EARLIER('Table'[Plugin ID])&&'Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<=EARLIER('Table'[Open Date])))
Return
_count

    And you wil see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

king2005r's avatar
king2005r
Helper III
5 years ago

Hi v-kelly-msft , thanks for the reply

If the today date is 1/1/2021

I expect something like the below table :

Plugin IDHostOpen DateRepeatedStatusCount of Plugin vulnerabilityToday
1026311.22.33.1161-Aug-20Not repeatedOpen11-Jan-21
1038612.33.44.231-Aug-20Not repeatedOpen11-Jan-21
1026311.22.33.1161-Sep-20repeatedOpen11-Jan-21
1038912.33.44.231-Sep-20Not repeatedClosed11-Jan-21
1024411.22.33.661-Oct-20Not repeatedOpen11-Jan-21
1034712.33.44.231-Oct-20Not repeatedOpen11-Jan-21
1024411.22.33.661-Nov-20repeatedOpen21-Jan-21
1034712.33.44.231-Nov-20repeatedOpen21-Jan-21
1026311.22.33.1161-Dec-20repeatedopen21-Jan-21
1038612.33.44.231-Dec-20repeatedopen21-Jan-21
1026311.22.33.1161-Jan-21repeatedopen31-Jan-21
1038612.33.44.231-Jan-21repeatedopen31-Jan-21
v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi king2005r ,

 

Create 2 calculated columns as below:

Repeated = 
 var _previous=CALCULATE(MAX('Table'[Plugin ID]),FILTER('Table','Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<EARLIER('Table'[Open Date])&&'Table'[Plugin ID]=EARLIER('Table'[Plugin ID])))
 Return
 IF(_previous=BLANK(),"Not repeated","Repeated")
Count of Plugin vulnerability = 
var _count=CALCULATE(COUNT('Table'[Plugin ID]),FILTER('Table','Table'[Plugin ID]=EARLIER('Table'[Plugin ID])&&'Table'[Host]=EARLIER('Table'[Host])&&'Table'[Open Date]<=EARLIER('Table'[Open Date])))
Return
_count

And you wil see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!