---
title: Sample Query: Missing Patches by Agent Policy
slug: isec/help/enu/DP6--sample-query-missing-patches-by-agent-policy
createdAt: 2026-08-05T05:48:31.646Z
---

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

## Query

```sql
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
    [Reporting3].[Agent] AS agent
INNER JOIN
    [Reporting3].[Machine] AS machine ON
    machine.[Id] = agent.[MachineId]
INNER JOIN
    [Reporting3].[AssessedMachineState] AS assessedMachineState ON
    assessedMachineState.[Id] = machine.[LastAssessedMachineStateId]
INNER JOIN
    [Reporting3].[WindowsDetectedPatchState] AS detectedPatchState ON
    detectedPatchState.[AssessedMachineStateId] = assessedMachineState.[Id]
INNER JOIN
    [Reporting3].[WindowsPatch] AS patch ON
    patch.[Id] = detectedPatchState.[PatchId]
INNER JOIN
    [Reporting3].[WindowsProduct] AS product ON
    product.[Id] = detectedPatchState.[ProductId]
INNER JOIN
    [Reporting3].[WindowsInstallState] 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]
```
