Sample Query: Patch Status Detail

This sample query gets a list of installed and missing patches from the latest scan for each machine.

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

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