October 12, 2011 at 6:11 pm
I have a set of data from computer scans that I am storing in a single table. The data is cumulative and contains duplicate VulnID / Host values. I have configured a view that uses a CTE with ROWCOUNT and PARTITION BY to only show me the last result by date for a specific VulnID / Host combination - it works
great!
Looking at the data below you will see that HOST 1 has lots of vulnerability data from scans that occurred on two different days (SCANCOMPLETETIME). The vulnerabilities that appear below with a SCANCOMPLETETIME of 2011-09-10 have been remediated. I know this because if they were found again the date would be 2011-10-10.
My challenge is getting a report that only shows the vulnerabilities associated with a host from the LASTSCAN (SCANCOMPELTETIME). As it is now I am reporting vulnerabilities that have been remediated. If a vulnID does not show up on a subsequent scan I do not want it to appear in the query results.
I have 10,000+ hosts and vulnerability data is constantly being added to the table that feeds the view. I do have a seperate hosts table.
Here is what the results look like today.
VulnIDHostSCANCOMPLETETIME
=======+=======+================
1233HOST12011-09-10
1234HOST12011-09-10
1235HOST12011-09-10
1246HOST12011-10-10
1246HOST12011-10-10
1247HOST12011-10-10
1238HOST12011-10-10
1239HOST12011-10-10
1250HOST12011-10-10
1251HOST12011-10-10
1252HOST12011-10-10
1246HOST22011-10-09
1247HOST22011-10-09
1238HOST22011-10-09
1239HOST22011-10-09
1250HOST22011-10-09
1251HOST22011-10-09
1252HOST22011-10-09
I wrote the following query that accomplishes what I want for a single host:
SELECT VulnID, Host, SCANCOMPLETETIME FROM VULNS_VIEW
WHERE NBNAME = 'HOST1'
and SCANCOMPLETETIME = (SELECT MAX(CANCOMPLETETIME) FROM VULNS_VIEW WHERE NBNAME = 'HOST1')
The results of this query show only the vulnIDs from the last scan (SCANCOMPLETETIME)
VulnIDHostSCANCOMPLETETIME
=======+=======+================
1246HOST12011-10-10
1246HOST12011-10-10
1247HOST12011-10-10
1238HOST12011-10-10
1239HOST12011-10-10
1250HOST12011-10-10
1251HOST12011-10-10
1252HOST12011-10-10
How can I accomplish the same feat with all the hosts in the data set? I suspect there is a way to do this with T-SQL where I load all of the distinct assets from the assets table into an array
and then find the MAX(SCANCOMPLETETIME) for each asset and then display the results for each host where SCANCOMPLETETIME = MAX(SCANCOMPLETETIME) for each host.
Thanks in advance for your advice!
October 13, 2011 at 2:03 am
You just need to reference the main query in the subquery. Something like this:
DECLARE @VULNS_VIEW TABLE (
VulnID int,
HOST varchar(10),
SCANCOMPLETETIME datetime
)
INSERT INTO @VULNS_VIEW
SELECT 1233,'HOST1','2011-09-10'
UNION ALL SELECT 1234,'HOST1','2011-09-10'
UNION ALL SELECT 1235,'HOST1','2011-09-10'
UNION ALL SELECT 1246,'HOST1','2011-10-10'
UNION ALL SELECT 1246,'HOST1','2011-10-10'
UNION ALL SELECT 1247,'HOST1','2011-10-10'
UNION ALL SELECT 1238,'HOST1','2011-10-10'
UNION ALL SELECT 1239,'HOST1','2011-10-10'
UNION ALL SELECT 1250,'HOST1','2011-10-10'
UNION ALL SELECT 1251,'HOST1','2011-10-10'
UNION ALL SELECT 1252,'HOST1','2011-10-10'
UNION ALL SELECT 1246,'HOST2','2011-10-09'
UNION ALL SELECT 1247,'HOST2','2011-10-09'
UNION ALL SELECT 1238,'HOST2','2011-10-09'
UNION ALL SELECT 1239,'HOST2','2011-10-09'
UNION ALL SELECT 1250,'HOST2','2011-10-09'
UNION ALL SELECT 1251,'HOST2','2011-10-09'
UNION ALL SELECT 1252,'HOST2','2011-10-09'
SELECT VulnID, HOST, SCANCOMPLETETIME
FROM @VULNS_VIEW AS VV
WHERE SCANCOMPLETETIME = (
SELECT MAX(SCANCOMPLETETIME)
FROM @VULNS_VIEW
WHERE HOST = VV.HOST
)
Hope this helps
Gianluca
-- Gianluca Sartori
October 13, 2011 at 4:48 am
Alternatively, you could use ROW_NUMBER to accomplish this:
SELECT VulnID, Host, SCANCOMPLETETIME FROM (
SELECT VulnID, Host, SCANCOMPLETETIME,
ROW_NUMBER() OVER (PARTITION BY Host, VulnID ORDER BY SCANCOMPLETETIME DESC) rw FROM VULNS_VIEW
) q
WHERE rw = 1
See which works best for you 🙂
October 13, 2011 at 5:59 am
Richard Warr (10/13/2011)
Alternatively, you could use ROW_NUMBER to accomplish this:SELECT VulnID, Host, SCANCOMPLETETIME FROM (
SELECT VulnID, Host, SCANCOMPLETETIME,
ROW_NUMBER() OVER (PARTITION BY Host, VulnID ORDER BY SCANCOMPLETETIME DESC) rw FROM VULNS_VIEW
) q
WHERE rw = 1
See which works best for you 🙂
Not exactly. ROW_NUMBER() would return a different result for rows with the same scanCompleteTime and we want it to return the same result.
DENSE_RANK() would be a better fit:
SELECT VulnID, Host, SCANCOMPLETETIME FROM (
SELECT VulnID, Host, SCANCOMPLETETIME,
DENSE_RANK() OVER (PARTITION BY Host ORDER BY SCANCOMPLETETIME DESC) rw
FROM @VULNS_VIEW
) q
WHERE rw = 1
Depending on your actual data and tables (VULNS_VIEW is a view, I don't know what's inside the view definition) you may get better execution plans for one or the other version.
-- Gianluca Sartori
October 13, 2011 at 8:57 pm
Thank you everyone for the replies. I was able to accomplish this by modifying the CTE that made up the view (VULNS_VIEW). This appears to be functioning as I've tested the results with hosts
that were showing multiple scan dates with distinct vulnID/Host combinations. This has given me what I needed which was to show only the results from the last scan for each host.
WITH SUMMARY AS (SELECT vulnid, host, scancompletetime,
ROW_NUMBER() OVER (PARTITION BY vulnid, host ORDER BY SCANCOMPLETETIME DESC) AS RANK FROM VULNS_TABLE
SELECT vulnid, host, scancompletetime
FROM SUMMARY x
WHERE x.rank = 1
AND x.SCANFINISHTIME = (SELECT MAX(SCANFINISHTIME)
FROM VULNS_TABLE
WHERE x.host = host)
October 14, 2011 at 2:32 am
Glad you sorted it out.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply