When count function doesn't get a hit, there is no entry - need a zero (1 Viewer)

cikVOU18

New member
Local time
Yesterday, 23:24
Joined
Sep 24, 2019
Messages
8
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.
 

ahmed_optom

Registered User.
Local time
Today, 06:24
Joined
Oct 27, 2016
Messages
93
you need to do something like this

if nz(your current null expression) then
set your value to "0" rather then null
else
end if
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:24
Joined
May 7, 2009
Messages
19,169
you may use SELECT DISTINCT:
Code:
select distinct [risk], 
    (select count("1") from [vulnerability info] as t1 
      where t1=[risk] = [vulnerability info].[risk] and t1.VulnPresentFor <= 30) 
      as [Average Time to Remediate < 30 days]
from [vulnerability info];
 

cikVOU18

New member
Local time
Yesterday, 23:24
Joined
Sep 24, 2019
Messages
8
Thanks for the replies. arnelgp, I'm trying to work through your suggestion, but I'm having a problem understanding "where t1=[risk] = [vulnerability info].[risk]" on the third line. Do you mind elaborating? Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:24
Joined
May 7, 2009
Messages
19,169
sorry, its :

t1.[risk]=[vulnerability info].[risk]
 

cikVOU18

New member
Local time
Yesterday, 23:24
Joined
Sep 24, 2019
Messages
8
Thanks, arnelgp. I figured that's what it was, but when I make this change, I don't get a result (the run won't stop) and I have to forcibly close Access and restart. I'm pretty new to this, but it seems reasonable that I should be able to run what's in the parenthesis by itself, something like the following, correct:

select count("1") from [vulnerability info] as t1
where t1.[risk] = [vulnerability info].[risk] and t1.VulnPresentFor <= 30
as [Average Time to Remediate < 30 days];

This seems reasonable, but I can't get this to work either (syntax error (missing operator)). I know I'm doing something wrong here, but I'll continue working on this.

Update: The above query runs when I remove "where t1=[risk] = [vulnerability info].[risk]", but LOW, MEDIUM, HIGH, and CRITICAL counters are all identical.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:24
Joined
May 7, 2009
Messages
19,169
if there are only 4 Risks involve,
you can manually do the counting by using UNION query:
Code:
SELECT TOP 1 1 AS ID, "LOW" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="LOW" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 2 AS ID, "MEDIUM" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="MEDIUM" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 3 AS ID, "HIGH" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="HIGH" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
UNION
SELECT TOP 1 4 AS ID, "CRITICAL" AS Risk, 
(SELECT COUNT("1") FROM [Vulnerability Info] AS T1 
    WHERE T1.Risk="CRITICAL" AND T1.[VulnPresentFor]<=30) AS [Average Time to Remediate < 30 days] FROM [Vulnerability Info]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:24
Joined
Feb 19, 2002
Messages
42,981
I would have used a crosstab. It would be more efficient on a large set of data.
 

Users who are viewing this thread

Top Bottom