Sample Query: CVE Vulnerability Report

The Protect 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 Protect view.

Query

This is a CVE vulnerability report that will display the CVE name and how many machines are affected due to a missing patch on the latest patch scan.

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

[Reporting].[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];