サンプル クエリ: CVE 脆弱性レポート
Security Controls ビューでは、Ivanti Security Controls データベース内にはあるが定義済みレポートに示されないデータにアクセスできます。 このセクションでは、Security Controls ビューの参照方法を例証するサンプル クエリを示します。
クエリ
これは、CVE 名と、最新パッチ スキャンで見つからないパッチがあった場合に何台のコンピュータが影響を受けるかを表示する CVE 脆弱性レポートです。
コピー
SELECT
cve.[Name] AS [CVE Name],
cve.[Id] AS [CVE ID],
patch.[Bulletin] AS [Bulletin Id],
patch.[QNumber] AS QNumber,
COUNT( DISTINCT machine.[Id]) AS [Machines Missing Count]
FROM
[Reporting2].[Machine] AS machine
INNER JOIN
[Reporting2].[AssessedMachineState] AS latestAssessedMachineState ON
latestAssessedMachineState.[machineId] = machine.[Id] AND
latestAssessedMachineState.[Id] = machine.[LastAssessedMachineStateId]
INNER JOIN
[Reporting2].[DetectedPatchState] AS detectedPatchState ON
detectedPatchState.[AssessedMachineStateId] = latestAssessedMachineState.[Id]
INNER JOIN
[Reporting2].[InstallState] AS installState ON
installState.[Id] = detectedPatchState.[InstallStateId]
INNER JOIN
[Reporting2].[Patch] AS patch ON
patch.[Id] = detectedPatchState.[PatchId]
INNER JOIN
[Reporting2].[PatchAppliesTo] AS patchAppliesTo ON
patchAppliesTo.[PatchId] = patch.[Id]
INNER JOIN
[Reporting2].[Cve] AS cve ON
cve.[Id] = patchAppliesTo.[CveId]
WHERE
/* Id 4 indicates a missing patch */
installState.[Id] = 4
GROUP BY
cve.[Name],
cve.[Id],
patch.[Bulletin],
patch.[QNumber];