Sample Query: Missing Patches by Agent Policy

Select all missing patches for the last scan of machines associated to an agent policy. The following data is displayed:

• Assigned Policy Name

• Machine Name

• Bulletin Id

• QNumber

• Product Name

• Service Pack

• Patch Install State

SELECT

agent.[AssignedPolicyName] AS [Assigned Policy Name],

machine.[Name] AS [Machine Name],

machine.[Domain] AS Domain,

patch.[Bulletin] AS [Bulletin Id],

patch.[QNumber] AS QNumber,

product.[Name] AS [Product Name],

product.[ProductLevelName] AS [Product Level],

installState.[Value] AS [Patch Install State],

product.[id] AS ProductId,

detectedPatchState.[ProductId] AS [detectedPatchState ProductID]

FROM

[Reporting2].[Agent] AS agent

INNER JOIN

[Reporting2].[Machine] AS machine ON

machine.[Id] = agent.[MachineId]

INNER JOIN

[Reporting2].[AssessedMachineState] AS assessedMachineState ON

assessedMachineState.[Id] = machine.[LastAssessedMachineStateId]

INNER JOIN

[Reporting].[DetectedPatchState] AS detectedPatchState ON

detectedPatchState.[AssessedMachineStateId] = assessedMachineState.[Id]

INNER JOIN

[Reporting2].[Patch] AS patch ON

patch.[Id] = detectedPatchState.[PatchId]

INNER JOIN

[Reporting2].[Product] AS product ON

product.[Id] = detectedPatchState.[ProductId]

INNER JOIN

[Reporting].[InstallState] AS installState ON

installState.[Id] = detectedPatchState.[InstallStateId]

WHERE

/* Id 4 indicates a missing patch */

installState.[Id] = 4

ORDER BY

agent.[AssignedPolicyName],

machine.[Name],

machine.[Domain],

patch.[Bulletin]