6 Replies Latest reply on Jan 2, 2018 8:50 AM by gwiner

    Custom Report including CVE

    gwiner Rookie

      Hi,

      I've been trying to write a custom report in the SQL Server back-end (starting from your Patch Status Detail example).  I've added a few new fields from reference tables but I am having a lot of trouble getting Reporting.Cve to join properly with Reporting.PatchAppliesTo.  It seems to be producing some quasi-Cartesian product and pulling in CVE's that do not have anything to do with Windows O/S products.  Do you already have a variation of the Patch Status Detail Report that includes the CVE?

      Thanks in advance ...

        • 1. Re: Custom Report including CVE
          cwinning CommunityTeam

          Hello,

           

          Looking into this now, I should know more later today.

           

          Thanks,

          Charles

          • 2. Re: Custom Report including CVE
            cwinning CommunityTeam

            Hello,

             

            You need to join the PatchAppliesTo and Cve table.

             

            INNER JOIN

            [Reporting].[PatchAppliesTo] pat ON

            product.[id] = pat.ProductId AND

            patch.[id] = pat.PatchId

            INNER JOIN

            [Reporting].[Cve] c ON

            pat.CveId = c.Id

             

            I hope that helps,

            Charles

            • 3. Re: Custom Report including CVE
              gwiner Rookie

              Hi Charles,

              Essentially, that's what I was doing (using old-fashioned syntax).  Still getting odd results. Like CVE-2015-5771 which is for an Apple OS X operating system component and it's tied to Microsoft Bulletin ID MS15-101 and some Q Number that's not part of that bulletin or product (.NET Framework 4.5.1).  Seriously?  I also get the correct Q Numbers for that Bulletin in my result set, but I don't need the extra stuff.  Here's the full SQL.  Maybe you can see my error?

               

              use ShavlikScans;

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

              /  edited to add columns for CH requirements */

              SELECT DISTINCT

              patch.[QNumber] AS [QNumber],

              patch.[Bulletin] AS [Bulletin Id],

              cve.[Name] AS [CVE Name],

              patch.[ReleasedOn] AS [Released On],

              product.[Name] AS [Product Name],

              product.[ServicePackName] AS [Service Pack Name],

              vendorSeverity.[Value] AS [Severity],

              patchType.[Value] AS [Type],

              installState.[Value] AS [Install state],

              patchScan.[StartedOn] AS [ScanStartDate],

              machine.[Name] AS [Machine Name],

              machine.[Domain] AS Domain,

              machine.[DnsName] AS DnsName,

              locale.[name] AS [Language Name],

              machine.[LastKnownIP] AS [IP Address],

              machine.[LastPatchAssessedOn] AS [ScanEndDate],

              detectedPatchState.[InstalledOn] AS [Installed On]

              FROM

              [Reporting].[VendorSeverity] AS vendorSeverity,

              [Reporting].[PatchType] AS patchType,

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

              LEFT OUTER JOIN

              [sys].[syslanguages] AS locale ON

              machine.[Language] = locale.[lcid] /* machine.[Language] is used to index into [sys].[syslanguages] */

              INNER JOIN

              [Reporting].[PatchAppliesTo] pat ON

              product.[id] = pat.ProductId AND

              patch.[id] = pat.PatchId

              INNER JOIN

              [Reporting].[Cve] cve ON

              pat.CveId = cve.Id

              WHERE

              UPPER(machine.[Name]) IN ('redacted') /* just leave this clause out for everything */

              and

              vendorSeverity.[Id]=patch.[VendorSeverityId] and

              patchType.[Id]=patch.[PatchTypeId] and

              (

              detectedPatchState.[InstallStateId] = 3 OR /* Installed Patch */

              detectedPatchState.[InstallStateId] = 4 OR /* Missing Patch */

              detectedPatchState.[InstallStateId] = 6 /* Missing Service Pack */

              )

              ORDER BY

              patch.[Bulletin],

              patch.[QNumber],

              machine.[Name]

              • 4. Re: Custom Report including CVE
                cwinning CommunityTeam

                Hello Gary,

                 

                I don't see anything wrong with the query.  One thing to note, Patch for Windows Servers 9.3 is Mac blind (for the moment) so it's odd CVE-2015-5771 is returning in the query.  I definitely can't find this CVE in View > Patches.

                 

                I went so far as to import the CVE using our API function to rule out a UI issue.

                 

                PS C:\Users\cwinning> Add-PatchGroupItem -Name 'Test' -Cve 'CVE-2015-5771'

                Adding the Protect API module dependencies

                Module Root Path: C:\Program Files\LANDESK\Shavlik Protect\PSModules\STProtect

                Adding the Protect API module dependencies from: C:\Program Files\LANDESK\Shavlik Protect\PSModules\..\ST.PSModule.Protect.dll

                WARNING: Cve item was not found: CVE-2015-5771

                 

                Is there something I'm missing in your setup?  Or missing in general?

                 

                Best regards,

                Charles

                • 5. Re: Custom Report including CVE
                  cwinning CommunityTeam

                  Hello,

                   

                  I was notified you also have a case open and it was escalated to a developer for review.  Could you please post the ultimate outcome was the issue is resolved?

                   

                  Thanks!

                  Charles

                  • 6. Re: Custom Report including CVE
                    gwiner Rookie

                    Hi Charles,

                    Case is still open, but I will post outcome when it is resolved.  I'm not sure how it came to pass (I'm the new guy around here) but it smells like we have bad data in our CVE table, possibly others.  Also some discussion as to the validity of the lookup tables for patchType and vendorSeverity being in my database, but since they are in your documentation I think that they should be there.

                    Thanks,

                    Gary