Sample Query: Patch Status Detail

SELECT DISTINCT
    patch.[QNumber] AS [QNumber],
    patch.[Bulletin] AS [Bulletin Id],
    patch.[ReleasedOn] AS [Released On],
    product.[Name] AS [Product Name],
    product.[ProductLevelName] AS [Product Level Name],
    installState.[Value] AS [Install state],
    patchScan.[StartedOn] AS [ScanDate],
    machine.[Name] AS [Machine Name],
    machine.[Domain] AS Domain,
    locale.[name] AS [Language Name],
    machine.[LastKnownIP] AS [IP Address],
    machine.[LastPatchAssessedOn] AS [Scan Date],
    detectedPatchState.[InstalledOn] AS [Installed On]
FROM
    [Reporting2].[PatchScan] AS patchScan
INNER JOIN
    [Reporting2].[AssessedMachineState] AS assessedMachineState ON
    assessedMachineState.[PatchScanId] = patchScan.[Id]
INNER JOIN
    [Reporting2].[Machine] AS machine ON
    machine.[LastAssessedMachineStateId] = assessedMachineState.[id]
INNER JOIN
    [Reporting2].[DetectedPatchState] AS detectedPatchState ON
    detectedPatchState.[AssessedMachineStateId] =  assessedMachineState.[Id]
INNER JOIN
    [Reporting2].[Patch] AS patch ON
    detectedPatchState.[PatchId] = patch.[Id]
INNER JOIN
    [Reporting2].[InstallState] AS installState ON
    installState.[Id] = detectedPatchState.[InstallStateId]
INNER JOIN
    [Reporting2].[Product] AS product ON
    product.[Id] = detectedPatchState.[ProductId]
LEFT OUTER JOIN
    [sys].[syslanguages] AS locale ON
    machine.[Language] = locale.[lcid] /* machine.[Language] is used to index into [sys].[syslanguages] */
WHERE
    (
    detectedPatchState.[InstallStateId] = 3 OR  /* Installed Patch */
    detectedPatchState.[InstallStateId] = 4     /* Missing Patch */
    )
ORDER BY
    patch.[Bulletin],
    patch.[QNumber],
    machine.[Name]