1 Reply Latest reply on Jan 26, 2018 10:53 AM by cwinning

    Report to simply list products installed on workstations (consolidated)

    bobsully Rookie

      I am being asked to provide a list of products that are installed on our workstation base.  Not per machine or per patch detail, but simply a list of all products installed consolidated into cumulative total for all.  I have been reading and experimenting but can't find a way to produce this, but was hoping a like issue was addresses by one of you here.

        • 1. Re: Report to simply list products installed on workstations (consolidated)
          cwinning CommunityTeam

          Hello,

           

          The list of installed products are directly related to the scan result for the machine, the following is based on the last scan perform against machines in your database.

          This uses out Report Views from this document:  Report Views Guide

           

          SELECT

              p.Name,

              p.ServicePackName,

              SUM(CASE WHEN (dps.[InstallStateId] = 4) THEN 1 ELSE 0 END) AS PatchMissing,

              SUM(CASE WHEN (dps.[InstallStateId] = 6) THEN 1 ELSE 0 END) AS ServicePackMissing,

              SUM(CASE WHEN (dps.[InstallStateId] IN (3,5)) THEN 1 ELSE 0 END) AS PatchFoundOrInstalled

          FROM

              [Reporting].[Machine] m

          INNER JOIN

              [Reporting].[AssessedMachineState] ams ON

                  ams.Id = m.LastAssessedMachineStateId

          INNER JOIN

              [Reporting].[DetectedPatchState] dps ON

                  dps.AssessedMachineStateId = ams.Id

          INNER JOIN

              [Reporting].[Product] p ON

                  p.Id = dps.ProductId

          GROUP BY

              p.Name,

              p.ServicePackName

          ORDER BY

              p.Name ASC,

              p.ServicePackName ASC

           

          Thanks,

          Charles