InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:06:22 GMT20http://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxThat'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:[url]http://www.sqlservercentral.com/articles/APPLY/69953/[/url] (part one)[url]http://www.sqlservercentral.com/articles/APPLY/69954/[/url] (part two)Thu, 12 Jan 2012 21:21:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxThanks 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:[CODE]SERVERIP [BASESCORE] [COUNT OF CRIT VIDs]192.168.0.1 9 5192.168.0.2 8 4192.168.0.3 7 6192.168.0.4 8 3192.168.0.5 1 3[/CODE]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:[CODE]SELECT S.SERVERIP, c.BASESCORE, ISNULL(C.VIDCOUNT, 0) AS VIDCOUNTFROM ( -- Distinct server list SELECT DISTINCT vs.SERVERIPFROM dbo.VID_SERVER AS vs) AS SCROSS 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 SCOUTER 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;[/CODE]Thu, 12 Jan 2012 17:56:29 GMTc2khttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxGive this a whirl:[code="sql"]SELECT S.SERVERIP, ISNULL(C.VIDCOUNT, 0) AS VIDCOUNTFROM ( -- Distinct server list SELECT DISTINCT vs.SERVERIP FROM dbo.VID_SERVER AS vs) AS SCROSS 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 SCOUTER 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;[/code]Wed, 11 Jan 2012 17:40:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxThanks Paul.I have the following based on ColdCoffee's suggestion.[CODE]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 ALEFT JOIN VULNS B ON b.vid = a.vid)GROUP BY SERVERIPORDER BY [COUNT] DESC[/CODE]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!Wed, 11 Jan 2012 16:21:58 GMTc2khttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspx[quote][b]c2k (1/11/2012)[/b][hr]Thank you for the replies. I am trying Paul's suggestion first which utilizes 'UNION ALL'[/quote]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.[quote]After reading up on 'UNION ALL' I have a general understanding of what it does. It will return the distinct results from the table.[/quote]No, UNION ALL just concatenates two sets without removing duplicates. UNION (without the ALL) is the one that includes an implicit DISTINCT.Wed, 11 Jan 2012 14:28:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxThank 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 periodI 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 [CODE][SCAN COMPLETE][/CODE] 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?----[CODE]SELECT U.ServerIP, BASESCORE COUNT_BIG(U.Union1001)FROM (SELECT e.ServerIP, NULL AS Union1001FROM @Example AS eLEFT JOIN VULNS D ON d.vid = e.vidWHERE d.BASESCORE >= 9AND EXPLOITABILITY IN('H','F','POC') UNION ALL SELECT e.ServerIP, 0 AS Union1001, BASESCOREFROM @Example AS eLEFT JOIN VULNS D ON d.vid = e.vidWHERE d.BASESCORE >= 9AND EXPLOITABILITY IN('H','F','POC')) AS UGROUP BY U.ServerIP, BASESCORE[/CODE]Wed, 11 Jan 2012 14:21:31 GMTc2khttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxJust 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:[code="sql"]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);[/code][code="sql"]-- Deprecated GROUP BY ALL syntax, do not useSELECT e.ServerIP, COUNT_BIG(*)FROM @Example AS eWHERE e.VID >= 9GROUP BY ALL e.ServerIP-- Equivalent query planSELECT 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 UGROUP BY U.ServerIP[/code]Wed, 11 Jan 2012 01:06:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxHow about this?[code="sql"]if object_id('tempdb..#t1') is not nulldrop table #t1create 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) CTfrom #t1GROUP BY SERVERIP [/code]Tue, 10 Jan 2012 19:16:21 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspxI 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 SERVERIPWHERE BASESCORE >= 9 and exploitabiltiy in ('H','POC','F'))SELECT V.SERVERIP, ISNULL(Summary.[COUNT],0) AS [COUNT]FROM VID_SERVER AS VLEFT JOIN Summary ON V.SERVERIP = Summary.SERVERIPTue, 10 Jan 2012 18:39:57 GMTandersg98http://www.sqlservercentral.com/Forums/Topic1233669-1292-1.aspx[CODE]VID SERVERIP BASESCORE EXPLOITABILITY SCANCOMPLETE51 192.168.0.1 5 H some date31 192.168.0.1 9 H some date42 192.168.0.2 3 POC some date64 192.168.0.2 3 H some date85 192.168.0.3 5 POC some date26 192.168.0.4 9 H some date37 192.168.0.4 9 POC some date[/CODE]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:[CODE]SERVERIP [COUNT OF CRIT VIDs]192.168.0.1 1192.168.0.2 0192.168.0.3 0192.168.0.4 2[/CODE]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'[CODE]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 NULLand BASESCORE >= 9and 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[/CODE]Thanks for your help!Tue, 10 Jan 2012 18:09:02 GMTc2k