Vulnerabilities status report monthly or yearly

[king2005r]

Hi

I have a table with monthly vulnerabilities, Plugin ID, and Host IP, and the open date, I need to do the below measurement  :

1- Get repeated monthly vulnerabilities

2- Get the count of the new vulnerabilities

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

4- Any vulnerabilities that exist in the last scan result to be considered as open in all previous months

Example table : 

Aug/2020

Plugin ID	Host	Open Date
10263	11.22.33.116	1/Aug/2020
10386	12.33.44.23	1/Aug/2020

Sep/2020

Plugin ID	Host	Open Date
10263	11.22.33.116	1/Sep/2020
10389	12.33.44.23	1/Sep/2020

Oct/2020

Plugin ID	Host	Open Date
10244	11.22.33.66	1/Oct/2020
10347	12.33.44.23	1/Oct/2020

Sample of the output :

Plugin ID	Host	Open Date	Repeated	Status	Count of Plugin vulnerability	Today
10263	11.22.33.116	1-Aug-20	Not repeated	Open	1	1-Jan-21
10386	12.33.44.23	1-Aug-20	Not repeated	Open	1	1-Jan-21
10263	11.22.33.116	1-Sep-20	repeated	Open	2	1-Jan-21
10389	12.33.44.23	1-Sep-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10347	12.33.44.23	1-Oct-20	Not repeated	Closed	1	1-Jan-21
10244	11.22.33.66	1-Nov-20	repeated	Closed	2	1-Jan-21
10347	12.33.44.23	1-Nov-20	repeated	Closed	2	1-Jan-21
10263	11.22.33.116	1-Dec-20	repeated	open	3	1-Jan-21
10386	12.33.44.23	1-Dec-20	repeated	open	2	1-Jan-21
10263	11.22.33.116	1-Jan-21	repeated	open	4	1-Jan-21
10386	12.33.44.23	1-Jan-21	repeated	open	3	1-Jan-21

[lbendlin · Accepted solution]

You can do this with calculated columns

Repeated = 
var o = Vulnerabilities[Open Date]
var c = CALCULATE(COUNTROWS(Vulnerabilities),ALLEXCEPT(Vulnerabilities,Vulnerabilities[Plugin ID]),Vulnerabilities[Open Date]<o)
return if(c=0,"Not repeated","repeated")

Status = 
var m = maxx(all(Vulnerabilities),Vulnerabilities[Open Date])
var x = CALCULATE(COUNTROWS(Vulnerabilities),allexcept(Vulnerabilities,Vulnerabilities[Plugin ID]),Vulnerabilities[Open Date]=m)
return if(x=0,"Closed","Open")

I don't understand the logic for the count column.

[lbendlin · Accepted solution]

Count = 
var o = Vulnerabilities[Open Date]
return CALCULATE(COUNTROWS(Vulnerabilities),ALLEXCEPT(Vulnerabilities,Vulnerabilities[Plugin ID]),Vulnerabilities[Open Date]<=o)

[lbendlin · Accepted solution]

You can do this with calculated columns

Repeated = 
var o = Vulnerabilities[Open Date]
var c = CALCULATE(COUNTROWS(Vulnerabilities),ALLEXCEPT(Vulnerabilities,Vulnerabilities[Plugin ID]),Vulnerabilities[Open Date]<o)
return if(c=0,"Not repeated","repeated")

Status = 
var m = maxx(all(Vulnerabilities),Vulnerabilities[Open Date])
var x = CALCULATE(COUNTROWS(Vulnerabilities),allexcept(Vulnerabilities,Vulnerabilities[Plugin ID]),Vulnerabilities[Open Date]=m)
return if(x=0,"Closed","Open")

I don't understand the logic for the count column.

[king2005r]

Hi @lbendlin 

i will try your solutions

regards the count column 

it's count repeated time per month for each plugin id 

plugin id number : 10263 repeated in jan 4 time's 

[king2005r]

@lbendlin

Hi bro, sorry i cant send massage

 i have tried the above foumla for status and dos'nt work, resoult was incorrect

actullay i need to do the below steps :

1- after get the last month report i added to Powerbi, this report has last scan information "last month"

2- Forumla to check all prevuois record for the past period for the host name "IP" with 3 conduction "Plugin ID" and "Host IP" and "Port number"

3- resoult if the three condution are equal to last scan resoult it mean this vulnerability still open, if not so it's closed in all previos months

4- Closed mean this vulnerability is not exist in the last scan report "last month""