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
Copy 
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
    [Reporting2].[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
    [Reporting2].[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]