Блог

Я пытаюсь написать запрос, который суммирует уязвимости по имени хоста и включает информацию об этом хосте. Запрос выполняется в 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 or 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)