Sample Query: CVE Vulnerability Report

The Security Controls views provide access to data that is in the Ivanti Security Controls database but that is not available in the predefined reports. This section provides sample queries that illustrate how to reference a Security Controls view.

This is a CVE vulnerability report that displays the CVE name and how many machines are affected because of a missing patch on the latest patch scan.

Query

Copy

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
    [Reporting3].[Machine] AS machine
INNER JOIN
    [Reporting3].[AssessedMachineState] AS latestAssessedMachineState ON
    latestAssessedMachineState.[machineId] = machine.[Id] AND
    latestAssessedMachineState.[Id] = machine.[LastAssessedMachineStateId]
INNER JOIN
    [Reporting3].[WindowsDetectedPatchState] AS detectedPatchState ON
    detectedPatchState.[AssessedMachineStateId] = latestAssessedMachineState.[Id]
INNER JOIN
    [Reporting3].[WindowsInstallState] AS installState ON
    installState.[Id] = detectedPatchState.[InstallStateId]
INNER JOIN
    [Reporting3].[WindowsPatch] AS patch ON
    patch.[Id] = detectedPatchState.[PatchId]
INNER JOIN
    [Reporting3].[WindowsPatchAppliesTo] AS patchAppliesTo ON
    patchAppliesTo.[PatchId] = patch.[Id]
INNER JOIN
    [Reporting3].[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];