Sample Query: Patch Status Detail
This sample query gets a list of installed and missing patches from the latest scan for each machine.
Copy
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]