1 Reply Latest reply on Oct 10, 2017 9:46 AM by cwinning

    Help Needed for Patching Report

    aarvelobt Rookie

      I've been asked to pull a report that includes machine names, name of missing patches, kb numbers and severity.  Looking at the Report Views Guide for 9.3 i see that there is a sample script that does almost exactly what I need.  It's on page 18 but can post the query if anyone needs me to.  I'm not too well versed with SQL querying but was wondering how we could add the severity for the patches onto that query?

        • 1. Re: Help Needed for Patching Report
          cwinning CommunityTeam

          Hello,

           

          Vendor Severity was added, try this:

           

          SELECT DISTINCT

          patch.[QNumber] AS [QNumber],

          patch.[Bulletin] AS [Bulletin Id],

          patch.[ReleasedOn] AS [Released On],

          product.[Name] AS [Product Name],

          product.[ServicePackName] AS [Service Pack 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],

          vendorSeverity.[Value] AS [Severity],

          vendorSeverity.[Description] AS [Severity Description]

          FROM

          [Reporting].[PatchScan] AS patchScan

          INNER JOIN

          [Reporting].[AssessedMachineState] AS assessedMachineState ON

          assessedMachineState.[PatchScanId] = patchScan.[Id]

          INNER JOIN

          [Reporting].[Machine] AS machine ON

          machine.[LastAssessedMachineStateId] = assessedMachineState.[id]

          INNER JOIN

          [Reporting].[DetectedPatchState] AS detectedPatchState ON

          detectedPatchState.[AssessedMachineStateId] = assessedMachineState.[Id]

          INNER JOIN

          [Reporting].[Patch] AS patch ON

          detectedPatchState.[PatchId] = patch.[Id]

          INNER JOIN

          [Reporting].[InstallState] AS installState ON

          installState.[Id] = detectedPatchState.[InstallStateId]

          INNER JOIN

          [Reporting].[Product] AS product ON

          product.[Id] = detectedPatchState.[ProductId]

          INNER JOIN

          [Reporting].[VendorSeverity] AS vendorSeverity ON

          vendorSeverity.[Id] = patch.VendorSeverityId

          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]

           

          Thanks,

          Charles