We have various vulnerability scans that send out a CSV file upon completion. The various sites are each put into a separate table, and then a query is run to determine how many vulnerabilities are present for length of time. For example, the following simply calculated whether a vulnerability has been present for 0 to 30 days, while other queries look at other lengths of time, e.g. < 30 days, 31-90 days, 91-180 days, etc. A typical query is as follows:
SELECT Count([Vulnerability Info].VulnPresentFor) AS [Average Time to Remediate < 30 days], [Vulnerability Info].Risk AS Risk
FROM [Vulnerability Info]
WHERE ((([Vulnerability Info].[VulnPresentFor])<=30))
GROUP BY [Vulnerability Info].Risk;
I need 4 rows for Critical, High, Medium, and Low. The problem is that if COUNT doesn't find anything for any of these, there is no entry. The result is that there may only be 2 or 3 rows instead of all 4.
Can anyone suggest what I might try to fix this? I've search Google for a couple hours and found the "nz" function, but I don't see how that applies in this case.
Thank you.
SELECT Count([Vulnerability Info].VulnPresentFor) AS [Average Time to Remediate < 30 days], [Vulnerability Info].Risk AS Risk
FROM [Vulnerability Info]
WHERE ((([Vulnerability Info].[VulnPresentFor])<=30))
GROUP BY [Vulnerability Info].Risk;
I need 4 rows for Critical, High, Medium, and Low. The problem is that if COUNT doesn't find anything for any of these, there is no entry. The result is that there may only be 2 or 3 rows instead of all 4.
Can anyone suggest what I might try to fix this? I've search Google for a couple hours and found the "nz" function, but I don't see how that applies in this case.
Thank you.