Thanks a lot Paul! I got the script working in our environment and my initial QA work indicates that the numbers are spot on. I am still trying to figure out how the whole APPLY things works - I'll get it.

In any case, I played around with the script trying to extend it's functionality to include counts of BASESCORE Values other than 9+. The output would look something like this:

SERVERIP [BASESCORE][COUNT OF CRIT VIDs]

192.168.0.19 5

192.168.0.28 4

192.168.0.37 6

192.168.0.48 3

192.168.0.51 3

Basically a count of vulnerabilities by BASESCORE that are 'H','F', or 'POC'. When I try this I end up breaking the query. One of the factors is that EXPLOITABILITY is actually in decimal form (ie, 9.34531). I've been using LEFT(EXPLOITABILITY,1) to convert these to '9'...there are none that are '10' so I haven't run into a problem with this method.

My attempt to do this looks something like this:

SELECT S.SERVERIP, c.BASESCORE, ISNULL(C.VIDCOUNT, 0) AS VIDCOUNTFROM

(

-- Distinct server list

SELECT DISTINCT vs.SERVERIP

FROM dbo.VID_SERVER AS vs

) AS S

CROSS APPLY

(

-- Most recent scan for each server

SELECT TOP (1) vs2.SCANCOMPLETE

FROM dbo.VID_SERVER AS vs2

WHERE

-- Correlate to distinct server list

vs2.SERVERIP = S.SERVERIP

ORDER BY vs2.SCANCOMPLETE DESC

) AS S

COUTER APPLY

(

-- Count qualifying VIDs

SELECT LEFT(v.basescore,1) AS BASESCORE, COUNT_BIG(DISTINCT vs3.VID) AS VIDCOUNT

FROM dbo.VID_SERVER AS vs3

JOIN dbo.VULNS AS v ON v.VID = vs3.VID

WHERE

-- Correlate to distinct server list

vs3.SERVERIP = S.SERVERIP

-- Correlate to latest scan date

AND vs3.SCANCOMPLETE = SC.SCANCOMPLETE

-- Qualifying exploitabilities

AND vs3.EXPLOITABILITY IN ('H', 'F', 'POC')

-- Minimum base score value from VULNS

AND v.BASESCOREVALUE >= 9) AS C;