我正在尝试编写一个查询,该查询按主机名汇总漏洞,并包含有关该主机的信息。 查询在 Rapid7 InsightVM 中运行
返回资产信息的查询成功运行,除非当我追加该查询以返回漏洞信息时,它会返回description
的不明确引用错误。 但是ip address
、host_name
和asset_id
值返回就可以了。
我只是试图将它们组合在一起以返回该信息。 我觉得缺少一些明显的东西。
这从资产表中返回我想要的内容,包括操作系统描述(Windows,RHEL等(:
SELECT da.asset_id, da.host_name, da.ip_address, dos.description
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN fact_asset fa ON fa.asset_id = da.asset_id
GROUP BY da.asset_id, da.host_name, da.ip_address, dos.description
这将返回描述的不明确引用,它适用于asset_id、host_name和ip_address:
WITH remediations AS (
SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id
FROM fact_remediation(10,'riskscore DESC') fr
JOIN dim_solution ds USING (solution_id)
JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)
),
assets AS (
SELECT da.asset_id, da.host_name, da.ip_address, dos.description
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN fact_asset fa ON fa.asset_id = da.asset_id
GROUP BY da.asset_id, da.host_name, da.ip_address, dos.description
)
SELECT
csv(DISTINCT dv.title) AS "Vulnerability Title",
host_name AS "Asset Hostname", ip_address AS "Asset IP", description AS "OS",
round(sum(dv.riskscore)) AS "Asset Risk",
summary AS "Solution",
fix as "Fix"
FROM remediations r
JOIN dim_asset_vulnerability_solution dvs USING (solution_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN assets USING (asset_id)
GROUP BY r.riskscore, host_name, ip_address, asset_id, summary, fix
ORDER BY "Asset Risk" DESC WITH remediations AS (
最有可能的是,dim_asset_vulnerability_solution
或dim_vulnerability
也有一个description
字段。只需使用其预期来源限定所选字段即可解决此问题。
...
a.host_name AS "Asset Hostname", a.ip_address AS "Asset IP", a.description AS "OS"
...
JOIN assets AS a USING (asset_id)
...
GROUP BY r.riskscore, a.host_name, a.ip_address, asset_id, summary, fix
注意:asset_id
不是问题,因为 USING 有一些额外的"魔力"可以合并它连接的引用。
评论:除非有非常具体的原因,否则不应将GROUP BY
用作SELECT DISTINCT
的替代品(特别是指CTE资产(