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- Any vulnerabilities don't exist in the last scan month vulnerabilities consider as closed in all previous months 

4- Any vulnerabilities exist in the last scan month consider as open in all previous months

 

Uniq column are Plugin ID and Host IP 

I try the below Dax but doesn't give me the correct numbers as wanted 

 
Closed Vulnerabilites = if(ISBLANK(Maxx(FILTER('Vulnerabilites-Report','Vulnerabilites-Report'[Host]=EARLIER('Vulnerabilites-Report'[Host]) && 'Vulnerabilites-Report'[Plugin ID]=EARLIER('Vulnerabilites-Report'[Plugin ID]) && 'Vulnerabilites-Report'[Open Date].[MonthNo]> EARLIER('Vulnerabilites-Report'[Open Date].[MonthNo])),'Vulnerabilites-Report'[Open Date].[Date])),0,1)

 

Repeated Count New = if(ISBLANK(Maxx(FILTER('Vulnerabilites-Report','Vulnerabilites-Report'[Host]=EARLIER('Vulnerabilites-Report'[Host]) && 'Vulnerabilites-Report'[Plugin ID]=EARLIER('Vulnerabilites-Report'[Plugin ID]) && 'Vulnerabilites-Report'[Open Date].[MonthNo]< EARLIER('Vulnerabilites-Report'[Open Date].[MonthNo]) && DATEADD('Vulnerabilites-Report'[Open Date].[Date],-1,MONTH)),'Vulnerabilites-Report'[Open Date].[Date])),0,1)

 

Example table : 

Aug/2020

Plugin IDHostOpen Date
1026311.22.33.1161/Aug/2020
1038612.33.44.231/Aug/2020

Sep/2020

Plugin IDHostOpen Date
1026311.22.33.1161/Sep/2020
1038912.33.44.231/Sep/2020

 

Oct/2020

Plugin IDHostOpen Date
1024411.22.33.661/Oct/2020
1034712.33.44.231/Oct/2020
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!

7 Replies

    • king2005r's avatar
      king2005r
      Helper III
      5 years ago

      Thanks a lot for your reply, but unfortunately, using a new formula is not currently possible. If it is possible to modify the existing formula, this will be the best and fastest solution instead of rebuilding all the formulas from the beginning

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi king2005r ,

     

    What is your expected output based on your sample data?

     

    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!

  • king2005r's avatar
    king2005r
    Helper III
    5 years ago

    Hi v-kelly-msft 

     

    sorry for this, I want to have the below if you can support in this further :

    1- Any vulnerabilities don't exist in the last scan month vulnerabilities consider as closed in all previous months 

    2- Any vulnerabilities exist in the last scan month consider as open in all previous months