August 20, 2013 at 3:30 pm
Table:
IPADDRESSSTR, NBNAME, FaultlineID, CVE, OPEN, CLOSED, STATUS
172.0.0.1, HOST1, 3623, CVE-2013-1212, 1, 0, vulnerable
172.0.0.2, HOST2, 3624, CVE-2013-1213, 0, 1, not vulnerale
172.0.0.3, HOST3, 3624, CVE-2013-1213 0, 1, not vulnerale
172.0.0.4, HOST4, 3624, CVE-2013-1213 1, 0, not vulnerale
172.0.0.5, HOST5, 3624, CVE-2013-1213 0, 1, not vulnerale
I would like to count the values in the OPEN and CLOSED column and calculate a total for each FID and CVE combination. The output should look like this:
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,2,3
I've tried the following SQL script, however, the counts are obviously wrong.
SELECT faultlineid, cve, SUM([OPEN]), SUM({CLOSED}),SUM([OPEN])+SUM({CLOSED}) AS TOTAL FROM TABLE
GROUP BY faultlineid, cve
How should I approach this problem in order to get accurate column counts?
August 20, 2013 at 3:35 pm
Why do you have this
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,2,3
Instead of this?
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,3,4
August 20, 2013 at 3:38 pm
An error with my math:
This is correct:
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,3,4
ty
August 20, 2013 at 3:45 pm
So the output from your query is correct?
The only difference I see is that you repeat the FID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply