6 Replies Latest reply on Mar 30, 2016 12:40 PM by cwinning

    Creating a custom report with pending patch count per product

    mbrownec Rookie

      Hello,

       

      The Director of IT here likes to see pending patch count per product.

       

      Meaning, he doesn't want to see pending patches per-machine and I think the executive summary is too shallow, not associating the missing patches with a product.

       

      With Secunia CSI, what I had been doing was creating a pivot table given a CSV data extraction/report to get this information.

       

       

      Is the report I wish to produce possible?

       

       

      Thanks,

       

      Matt

        • 1. Re: Creating a custom report with pending patch count per product
          cwinning CommunityTeam

          Hello,

           

          is your use of 'Pending' synonymous with 'Missing'?  If so, you wan to list products and how many missing patches they have?

           

          Thanks,

          Charles

          • 2. Re: Creating a custom report with pending patch count per product
            mbrownec Rookie

            Thanks for your reply!

             

            Yes, pending/missing would have the same meaning.

             

            I want to list products and how many machines are missing patches for this product:

             

            Given:

            • For the product “Adobe Flash” there are 50 patches that need to be installed on COMPUTERA, 30 patches that need to be installed on COMPUTERB and 1 patch to be installed on COMPUTERC.
            • For the product “Microsoft Word 2010,” there are 3 patches that need to be installed on COMPUTERD, and 1 patch to be installed on COMPUTERA.

             

            The resulting report would contain:

            • Adobe Flash: 3
            • Microsoft Word 2010: 2

             

            Meaning: Three computers need any quantity of patches for Adobe Flash, and two computers need any quantity of patches for Microsoft Word 2010.

             

            If, in addition to this summary, a later part of the report could contain a list of the machines who need the patches.

             

             

            What i don't want are the only reports I believe I can currently produce.

            In the given case noted above, I believe there are reports that would list the follow two pieces of information:

            Adobe Flash PATCH1: COMPUTERA, COMPUTERB, COMPUTERC

            Adobe Flash PATCH2: COMPUTERA, COMPUTERB, COMPUTERC

            Adobe Flash PATCH3: COMPUTERA, COMPUTERB, COMPUTERC

            etc

            or

            COMPUTERA: Adobe Flash PATCH1, Adobe Flash PATCH2, Adobe Flash PATCH3,... Microsoft Word 2010 PATCH1

            COMPUTERB: Adobe Flash PATCH1, Adobe Flash PATCH2, Adobe Flash PATCH3,...

            COMPUTERC: Adobe Flash PATCH1, Adobe Flash PATCH2, Adobe Flash PATCH3,...

            COMPUTERD: Adobe Flash PATCH1, Adobe Flash PATCH2, Adobe Flash PATCH3,... Microsoft Word 2010 PATCH1, Microsoft Word 2010 PATCH2, Microsoft Word 2010 PATCH3.

             

             

            So, to keep it short, i'd like a report that lists "[Missing] patch count per product."

             

            Secunia CSI was unable to produce this report through there (three) built-in reports.  Instead I was able to export the patch information to a CSV, and then used a pivot table, which was incredibly easy to filter and get the total count, etc., that my boss wanted.

             

             

            Thanks,

             

            Matt

            • 3. Re: Creating a custom report with pending patch count per product
              cwinning CommunityTeam

              Hello,

               

              There nothing built-in to give this type of data.  Normally, if a customer can't find a report to fit their needs, we point them toward two things;

               

              1. Report Views Guide
              2. The Feature Request page.

               

              Let me know,

              Charles

              • 4. Re: Creating a custom report with pending patch count per product
                mbrownec Rookie

                Thanks Charles!

                 

                Bobby from the Support team explained that resultant reports can be exported in a CSV format, and also that you can configure a Scheduled Report to Email a CSV report format,

                 

                However, none of the reports contain the actual product information.  So I so think I'll have to perform an SQL query.

                • 5. Re: Creating a custom report with pending patch count per product
                  mbrownec Rookie

                  I've reworked a the Patch Status Detail query noted at the end of the reports view guide PDF

                   

                   

                  SELECT DISTINCT
                  patch.[QNumber] AS [QNumber],
                  product.[Name] AS [Product Name],
                  installState.[Value] AS [Install state],
                  machine.[Name] AS [Machine Name]
                  
                  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]
                  LEFT OUTER JOIN
                  [sys].[syslanguages] AS locale ON
                  machine.[Language] = locale.[lcid] /* machine.[Language] is used to index into [sys].[syslanguages] */
                  WHERE
                  (
                  detectedPatchState.[InstallStateId] = -1 OR /* Not recorded */
                  detectedPatchState.[InstallStateId] = 0 OR /* Warning */
                  detectedPatchState.[InstallStateId] = 1 OR /* Note */
                  detectedPatchState.[InstallStateId] = 2 OR /* Informational */
                  detectedPatchState.[InstallStateId] = 3 OR /* Installed */
                  detectedPatchState.[InstallStateId] = 3 OR /* Installed Patch */
                  detectedPatchState.[InstallStateId] = 4 /* Missing Patch */
                  )
                  ORDER BY
                  product.[Name],
                  machine.[Name]
                  

                  I'll export this as a CSV and then use this to produce what I need.

                  • 6. Re: Creating a custom report with pending patch count per product
                    cwinning CommunityTeam

                    Thanks for taking the time to share this.

                     

                    Charles