Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count fields that do not meet specified condition Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 6:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
VID	SERVERIP	BASESCORE	EXPLOITABILITY	SCANCOMPLETE
51 192.168.0.1 5 H some date
31 192.168.0.1 9 H some date
42 192.168.0.2 3 POC some date
64 192.168.0.2 3 H some date
85 192.168.0.3 5 POC some date
26 192.168.0.4 9 H some date
37 192.168.0.4 9 POC 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.1 1
192.168.0.2 0
192.168.0.3 0
192.168.0.4 2

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!
Post #1233669
Posted Tuesday, January 10, 2012 6:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:04 PM
Points: 2,127, Visits: 747
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



Post #1233672
Posted Tuesday, January 10, 2012 7:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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

Post #1233679
Posted Wednesday, January 11, 2012 1:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233766
Posted Wednesday, January 11, 2012 2:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
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

Post #1234384
Posted Wednesday, January 11, 2012 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234389
Posted Wednesday, January 11, 2012 4:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
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!
Post #1234456
Posted Wednesday, January 11, 2012 5:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234488
Posted Thursday, January 12, 2012 5:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
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.1 9 5
192.168.0.2 8 4
192.168.0.3 7 6
192.168.0.4 8 3
192.168.0.5 1 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;


Post #1235324
Posted Thursday, January 12, 2012 9:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1235353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse