Using IN and a sub-query that ORDERS BY, and grouping

InsightVM SQL Queries
1

I have a query that lists all our assets with critical vulnerabilities and the associated solutions that I’d like to improve in two ways.

  1. I’d like it to only include the TOP 10 devices by total risk score per region in the results. To do this I’ve tried to use a sub query like this in a WHERE IN statement as below, but i get the error ‘for SELECT DISTINCT, ORDER BY expressions must appear in select list’, but if I add fa.riskscore to the SELECT list I get the error ‘subquery has too many columns’. I understand why i’m getting these errors (having worked with SQL a bit many years ago), but don’t understand how to get this too work
    …
    WHERE da.host_name IN
    (
    SELECT DISTINCT
    da.host_name AS “Hostname”,
    FROM
    dim_asset da
    JOIN fact_asset fa ON da.asset_id=fa.asset_id
    JOIN dim_tag_asset dta on dta.asset_id = da.asset_id
    JOIN dim_tag dt on dt.tag_id = dta.tag_id
    WHERE dt.tag_name = ‘Region 1 Support’
    ORDER BY fa.riskscore DESC
    LIMIT 10
    )

  2. The above query creates results for the Region 1 Support team tag. I’d like the report to include all Regions - one option for me is to create one report per region and merge the resulting data. Another, preferred, option is to include all the data in one report by having multiple WHERE IN subqueries for each Region, but that seems like it may create an long and probably inefficient query. Are there any other options to do this?

Thanks in advance for any advice

2

To answer one of my own questions - ORDER BY can’t be used in a sub-query. I’ll go back to the drawing board on this and upload what i find in case it ever helps anybody else.

3

I worked it out and have include it below for reference.

For reference, this extracts the top xx (change as required) devices in terms of risk score that have been active in the last 14 days per OWNER tag (i.e. tech or sys admin support team for example), and then lists all critical vulnerabilities on those devices that were published at least 30 days ago, along with the solutions (and ‘proof’ where additional info is needed).

I then take the results of this and use an Excel macro to ‘merge’ solutions (i.e. multiple Adobe Reader patches get merged into a single ‘Uninstall or Upgrade Adobe Reader’ line) and that gets split into a list per support team and emailed to them. This is then much more manageable for the support teams than it would otherwise be. The xx counts and date criteria can be altered over time depending on count of assets, how many critical issues exist, progress over time etc.

SELECT DISTINCT da.host_name
	,da.ip_address
	,dos.description
	,da.last_assessed_for_vulnerabilities
	,ds.summary AS Solution
	,dt.tag_name AS SupportTeam
	,proofAsText(favi.proof) AS "Proof"
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_asset_vulnerability_solution dasv ON dasv.asset_id = da.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = dasv.vulnerability_id
JOIN dim_solution ds ON ds.solution_id = dasv.solution_id
JOIN dim_tag_asset dta ON dta.asset_id = da.asset_id
JOIN dim_tag dt ON dt.tag_id = dta.tag_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
	AND favi.vulnerability_id = dv.vulnerability_id
WHERE 
	dv.cvss_score >= 9.0 AND
	dv.date_published < (CURRENT_DATE - 30) AND
	da.host_name IN (
		SELECT host_name
		FROM (
			SELECT da.host_name
				,da.ip_address
				,dos.description
				,dt.tag_name AS OWNER
				,fa.riskscore AS "Risk Score"
				,row_number() OVER (
					PARTITION BY dt.tag_name ORDER BY fa.riskscore DESC
					) AS risk_rank
			FROM dim_asset da
			JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
			JOIN dim_tag_asset dta ON dta.asset_id = da.asset_id
			JOIN dim_tag dt ON dt.tag_id = dta.tag_id
			JOIN fact_asset fa ON da.asset_id = fa.asset_id
			WHERE da.last_assessed_for_vulnerabilities > (CURRENT_DATE - 14)
				AND tag_type = 'OWNER'
			) ranks
		WHERE risk_rank <= xx
		)