January 10, 2012 at 6:09 pm
VIDSERVERIPBASESCOREEXPLOITABILITYSCANCOMPLETE
51192.168.0.15H some date
31192.168.0.19H some date
42192.168.0.23POC some date
64192.168.0.23H some date
85192.168.0.35POC some date
26192.168.0.49H some date
37192.168.0.49POC some date
Required output:
Need a count of the number of VIDs with a BASESCORE >= 9 summarized on each SERVERIP.
Problem:
Some servers do not have any VIDs with a BASESCORE >=9. I want these SERVERIPs to be represented in the results with a count of '0'
Desired output:
SERVERIP [COUNT OF CRIT VIDs]
192.168.0.11
192.168.0.20
192.168.0.30
192.168.0.42
The current query looks like this....the issue is that it does not show me the SERVERIP where [COUNT OF CRIT VIDs] = 0. The query below shows me a count of vulnerabilities (that meet the conditions in the where clause)
for each SERVERIP. My challenge is figuring out how to represent SERVERIPs that have no VIDs with a BASESCORE >=9 in the results with a count of '0'
WITH SUMMARY AS(
SELECT SERVERIP, VID, BASESCORE, exploitability, scancomplete,
ROW_NUMBER() OVER (PARTITION BY SERVERIP, VID ORDER BY SCANCOMPLETE DESC) AS RANK
FROM VID_SERVER
WHERE
BASESCOREVALUE IS NOT NULL
and BASESCORE >= 9
and exploitabiltiy in ('H','POC','F')
)
SELECT SERVERIP, [BASESCORE], SCANCOMPLETE, COUNT(*) AS [COUNT]
FROM SUMMARY x
WHERE x.rank = 1 AND x.SCANCOMPLETE =
(SELECT MAX(SCANCOMPLETE)
FROM VID_SERVER C
WHERE x.[SERVERIP] = C.SERVERIP)
GROUP BY SERVERIP, [BASESCORE], SCANCOMPLETE
ORDER BY [SERVERIP] DESC
Thanks for your help!
January 10, 2012 at 6:39 pm
I didn't have time to build a test table but I think something like this would work:
WITH Summary AS
(
SELECT
SERVERIP,
COUNT(*) AS [COUNT]
FROM
VID_SERVER
GROUP BY SERVERIP
WHERE BASESCORE >= 9 and exploitabiltiy in ('H','POC','F'))
SELECT V.SERVERIP, ISNULL(Summary.[COUNT],0) AS [COUNT]
FROM VID_SERVER AS V
LEFT JOIN Summary ON V.SERVERIP = Summary.SERVERIP
January 10, 2012 at 7:16 pm
How about this?
if object_id('tempdb..#t1') is not null
drop table #t1
create table #t1 ( SERVERIP varchar(20), BASESCORE int)
insert into #t1 (BASESCORE,SERVERIP)
values (5,'192.168.0.1')
, (9,'192.168.0.1')
, (3,'192.168.0.2')
, (3,'192.168.0.2')
, (5,'192.168.0.3')
, (9,'192.168.0.4')
, (9,'192.168.0.4')
select SERVERIP
, SUM( CASE WHEN BASESCORE >= 9 THEN 1 ELSE 0 END) CT
from #t1
GROUP BY SERVERIP
January 11, 2012 at 1:06 am
Just for interest, this can be written using the (now deprecated) GROUP BY ALL syntax. This example shows the equivalent query with and without using GROUP BY ALL:
DECLARE @Example TABLE
(
ServerIP varchar(20) NOT NULL,
VID integer NOT NULL
);
INSERT @Example
(ServerIP, VID)
VALUES
('192.168.0.1', 5),
('192.168.0.1', 9),
('192.168.0.2', 3),
('192.168.0.2', 3),
('192.168.0.3', 5),
('192.168.0.4', 9),
('192.168.0.4', 9);
-- Deprecated GROUP BY ALL syntax, do not use
SELECT
e.ServerIP,
COUNT_BIG(*)
FROM @Example AS e
WHERE
e.VID >= 9
GROUP BY ALL
e.ServerIP
-- Equivalent query plan
SELECT
U.ServerIP,
COUNT_BIG(U.Union1001)
FROM
(
SELECT
e.ServerIP,
NULL AS Union1001
FROM @Example AS e
UNION ALL
SELECT
e.ServerIP,
0 AS Union1001
FROM @Example AS e
WHERE
e.VID >= 9
) AS U
GROUP BY
U.ServerIP
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 2:21 pm
Thank you for the replies. I am trying Paul's suggestion first which utilizes 'UNION ALL'
The way the query is written below I get inflated results (counts) if a ServerIP host was scanned more than once during the period
I need to do the following next with the script:
- ensure that duplicates are removed (I was using a CTE for this previously)
- ensure that I am getting the last scan data for each IP address. (This was also part of my original CTE)
- to add [SCAN COMPLETE] columns to the final output:
After reading up on 'UNION ALL' I have a general understanding of what it does. It will return the distinct results from the table. But how can I ensure I am only counting the results from the MAX(SCANCOMPLETE) time for each host?
----
SELECT U.ServerIP, BASESCORE COUNT_BIG(U.Union1001)
FROM
(SELECT e.ServerIP, NULL AS Union1001
FROM @Example AS e
LEFT JOIN VULNS D ON d.vid = e.vid
WHERE d.BASESCORE >= 9
AND EXPLOITABILITY IN('H','F','POC')
UNION ALL
SELECT e.ServerIP, 0 AS Union1001, BASESCOREFROM @Example AS e
LEFT JOIN VULNS D ON d.vid = e.vid
WHERE d.BASESCORE >= 9
AND EXPLOITABILITY IN('H','F','POC')
) AS UGROUP BY U.ServerIP, BASESCORE
January 11, 2012 at 2:28 pm
c2k (1/11/2012)
Thank you for the replies. I am trying Paul's suggestion first which utilizes 'UNION ALL'
Please don't! My post was aimed at Cold Coffee, and was for interest only. You should base your code around Cold Coffee's solution.
After reading up on 'UNION ALL' I have a general understanding of what it does. It will return the distinct results from the table.
No, UNION ALL just concatenates two sets without removing duplicates. UNION (without the ALL) is the one that includes an implicit DISTINCT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 4:21 pm
Thanks Paul.
I have the following based on ColdCoffee's suggestion.
SELECT SERVERIP, 9 AS BASESCOREVALUE, SUM(CASE WHEN (b.BASESCOREVALUE >= 9 and exploitabiltiy IN('H','F','POC') )
THEN 1 ELSE 0 END) [COUNT]
FROM EXAMPLETABLE A
LEFT JOIN VULNS B ON b.vid = a.vid)
GROUP BY SERVERIP
ORDER BY [COUNT] DESC
Which returns the correct number of distinct SERVERIPs (no duplicated). How easily can I incorporate changes that would accomplish the following:
- Remove any duplicate VID, SERVERIP combinations (they do exists)...this is what my CTE was doing for me.
- Ensure I am only counting vulns from the last SCANCOMPLETE date. A single serverip could have been scanned multiple times during a given period.
Thanks!
January 11, 2012 at 5:40 pm
Give this a whirl:
SELECT
S.SERVERIP,
ISNULL(C.VIDCOUNT, 0) AS VIDCOUNT
FROM
(
-- 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 SC
OUTER APPLY
(
-- Count qualifying VIDs
SELECT
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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 5:56 pm
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;
January 12, 2012 at 9:21 pm
That's great news. I'm short of time right now, but I'll be back later to have a closer look (unless anyone else wants to chip in). In the meantime, here's a couple of links to my APPLY article, in case you haven't seen it before:
http://www.sqlservercentral.com/articles/APPLY/69953/ (part one)
http://www.sqlservercentral.com/articles/APPLY/69954/ (part two)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply