Determine & Display Number of Days Between CVE Found/Remediated or Found/Still Open

[jvansickler]

I've been struggling with this for a while.

We need to track how log it takes us to remediate CVEs (vulnerabilities) found in our Nessus scans, and how long the unresolved CVEs have been in the system. We're using a combination of the CVE, hostname, and unique hash of the Plugin output (CHP) as a key.

These examples will show how the CHP is used to tie the CVE Begin & End dates together, and how the CHP remains the same over multiple scans.

Scan Date	CVE	Host	Begin	End	CVE-HostKey-PluginOutputHash	cve_days_open
1/28/2024	CVE-2024-20918	examplehost	1/28/2024		cve-2024-20918-examplehost-a466957fef65cfd2a9416a8eeb5067d18d38af3a1b40e0b6d4a69bd189260624
1/29/2024	CVE-2024-20918	examplehost		1/29/2024	cve-2024-20918-examplehost-a466957fef65cfd2a9416a8eeb5067d18d38af3a1b40e0b6d4a69bd189260624
1/28/2024	CVE-2024-20918	examplehost	1/28/2024		cve-2024-20918-examplehost-f6176e330c108379f7c8fd1b5978a4e3296fb274d31cecb2b478445db1d2b030
4/15/2024	CVE-2024-20918	examplehost		4/15/2024	cve-2024-20918-examplehost-f6176e330c108379f7c8fd1b5978a4e3296fb274d31cecb2b478445db1d2b030
2/20/2024	CVE-2024-20918	examplehost	2/20/2024		cve-2024-20918-examplehost-c6f337b1626549bce201454fcf360e1aba07526bd1511b703aff03c93d31f1c9
2/24/2024	CVE-2024-20918	examplehost		2/24/2024	cve-2024-20918-examplehost-c6f337b1626549bce201454fcf360e1aba07526bd1511b703aff03c93d31f1c9
3/5/2024	CVE-2024-20918	examplehost	3/5/2024		cve-2024-20918-examplehost-ef289f0f3ad104a3e9c4b87f55dabf4631879f15a42ac5be8ea865976437eadc
3/18/2024	CVE-2024-20918	examplehost		3/18/2024	cve-2024-20918-examplehost-ef289f0f3ad104a3e9c4b87f55dabf4631879f15a42ac5be8ea865976437eadc

Scan Date	CVE	Host	Begin	End	CVE-HostKey-PluginOutputHash	cve_days_open
4/24/2024	CVE-2024-20918	examplehost	4/24/2024		cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
5/8/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
5/13/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
5/20/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
6/4/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
6/20/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
6/24/2024	CVE-2024-20918	examplehost			cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d
6/26/2024	CVE-2024-20918	examplehost		6/26/2024	cve-2024-20918-examplehost-b492473916cffcfe73b0000539f2273a8cf169cc11bc8d0cd55d47c39667ad7d

The table name is 'FactSourceCSV'

The elements would be:

'FactSourceCSV'[Begin]

'FactSourceCSV'[End]

'FactSourceCSV'[CVE-HostKey-PluginOutputHash]

The results/running sum would go into 'FactSourceCSV'[cve_days_open]

I don't know if this is supposed to be a measure, calculated column, or done in the visualization.  I've tried all three, with no success.  Filtering on the CHP never seems to work right.

[v-sdhruv]

Hi @jvansickler ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @jvansickler ,
You can try this-
cve_days_open =
VAR ThisKey = 'FactSourceCSV'[CVE-HostKey-PluginOutputHash]
VAR FirstBegin =
CALCULATE(
MIN('FactSourceCSV'[Begin]),
FILTER('FactSourceCSV', 'FactSourceCSV'[CVE-HostKey-PluginOutputHash] = ThisKey)
)
VAR EndDate =
IF(
ISBLANK('FactSourceCSV'[End]),
TODAY(),
'FactSourceCSV'[End]
)
RETURN
DATEDIFF(FirstBegin, EndDate, DAY)
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @jvansickler ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[bhanu_gautam]

@jvansickler You can create a calculated column in your table to calculate the number of days a CVE has been open. The formula will subtract the Begin date from the End date. If the End date is blank, it will use the current date to calculate the days open.

cve_days_open =
IF(
ISBLANK('FactSourceCSV'[End]),
DATEDIFF('FactSourceCSV'[Begin], TODAY(), DAY),
DATEDIFF('FactSourceCSV'[Begin], 'FactSourceCSV'[End], DAY)
)

If you have multiple scans and need to track the CVE across these scans, you might need to aggregate the data to get the first Begin date and the last End date for each CVE-HostKey-PluginOutputHash. This can be done using a summarized table

dax
SummaryTable =
SUMMARIZE(
'FactSourceCSV',
'FactSourceCSV'[CVE-HostKey-PluginOutputHash],
"FirstBegin", MIN('FactSourceCSV'[Begin]),
"LastEnd", MAX('FactSourceCSV'[End])
)

Then, you can calculate the cve_days_open based on this summarized table:

dax
cve_days_open =
IF(
ISBLANK(SummaryTable[LastEnd]),
DATEDIFF(SummaryTable[FirstBegin], TODAY(), DAY),
DATEDIFF(SummaryTable[FirstBegin], SummaryTable[LastEnd], DAY)
)

[jvansickler]

This works (thank you!), but the number of days returned is the reverse from what I am looking for.

e.g.:  

I need to display the CveDaysOpen count with the smallest number for the oldest scan date and the largest number (how long the CVE has been open) in the most recent scan date. right now, it's the inverse.

[maruthisp]

Hi @jvansickler ,

As per my understanding of your problem, a pattern you can use to calculate, per unique vulnerability instance (identified by your “CVE-HostKey-PluginOutputHash”), the number of days it was open whether it’s already remediated or still outstanding:

// As a calculated column in FactSourceCSV:
CVEDaysOpen =
VAR ThisKey =
FactSourceCSV[CVE-HostKey-PluginOutputHash]

VAR FirstSeen =
CALCULATE(
MIN( FactSourceCSV[Begin] ),
ALLEXCEPT( FactSourceCSV, FactSourceCSV[CVE-HostKey-PluginOutputHash] )
)

VAR ResolvedOn =
CALCULATE(
MAX( FactSourceCSV[End] ),
ALLEXCEPT( FactSourceCSV, FactSourceCSV[CVE-HostKey-PluginOutputHash] )
)

VAR LastScan =
CALCULATE(
MAX( FactSourceCSV[Scan Date] ),
ALLEXCEPT( FactSourceCSV, FactSourceCSV[CVE-HostKey-PluginOutputHash] )
)

// If there’s no End date yet, use the date of the latest scan
VAR CloseDate =
IF(
NOT( ISBLANK( ResolvedOn ) ),
ResolvedOn,
LastScan
)

RETURN
DATEDIFF( FirstSeen, CloseDate, DAY )

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X