List All Patches

USE SUSDB

SELECT DISTINCT

       CASE WHEN CHARINDEX('.',vCT.Name) > 0 THEN LEFT(vCT.Name,CHARINDEX('.',vCT.Name) - 1) ELSE vCT.Name END as [Computer Name]

       ,CASE WHEN CHARINDEX('.',vCT.Name) > 0 THEN RIGHT(vCT.Name,LEN(vCT.Name) - CHARINDEX('.',vCT.Name)) ELSE 'n/a' END as Domain

       ,vCT.OSArchitecture

       ,vCT.IPAddress

       ,CASE WHEN vCT.OSMajorVersion = 6 and vCT.OSMinorVersion = 1 THEN 'Windows 7'

              WHEN vCT.OSMajorVersion = 5 and vCT.OSMinorVersion = 1 THEN 'Windows XP'

              WHEN vCT.OSMajorVersion = 6 and (vCT.OSMinorVersion = 2 or vCT.OSMinorVersion = 3) THEN 'Windows 8'

              WHEN vCT.OSMajorVersion = 6 and vCT.OSMinorVersion = 0 THEN 'Windows Vista'

              WHEN vCT.OSMajorVersion = 5 and vCT.OSMinorVersion = 0 THEN 'Windows 2000'

              WHEN vCT.OSMajorVersion = 10  THEN 'Windows 10'

       ELSE 'Unknown' END AS OS

       ,vCT.OSServicePackMajorNumber AS 'SPV'

       ,vCT.OSDefaultUILanguage AS 'OS Language'

       ,vCT.Make AS 'Manufacturer'

       ,vCT.Model AS 'Model'

       ,g.GroupName

       ,CONVERT(char(10), vCT.LastSyncTime,126) AS 'Last Sync Time'

       ,DATEDIFF(day,  vCT.LastSyncTime, GETDATE()) AS 'Last Sync Days'

       ,vCT.LastSyncResult

       ,REPLACE( CAST(( CAST( Sum(case when fSM.Name IN ('Installed','NotApplicable') then 1 else 0 end) AS DECIMAL(10,2) ) / ( CAST( Sum(case when fSM.Name IN ('Installed','Failed','Unknown','NotApplicable','Downloaded','InstalledPendingReboot','NotInstalled') then 1 else 0 end) AS DECIMAL(10,2) ) / 100 ) ) AS DECIMAL(10,2) ),'.',',') AS [Success %]

       ,Sum(case when fSM.Name IN ('Installed','Failed','Unknown','NotApplicable','Downloaded','InstalledPendingReboot','NotInstalled') then 1 else 0 end) AS [Total]

       ,Sum(case when fSM.Name IN ('Installed','NotApplicable') then 1 else 0 end) AS [Installed]

       ,Sum(case when fSM.Name IN ('Failed') then 1 else 0 end) AS [Failed]

       ,Sum(case when fSM.Name IN ('NotInstalled') then 1 else 0 end) AS [NotInstalled]

       ,Sum(case when fSM.Name IN ('InstalledPendingReboot') then 1 else 0 end) AS [InstalledPendingReboot]

       ,Sum(case when fSM.Name IN ('Downloaded') then 1 else 0 end) AS [Downloaded]

       ,Sum(case when fSM.Name IN ('Unknown') then 1 else 0 end) AS [Unknown]

FROM

       PUBLIC_VIEWS.vUpdate AS vU

       INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic AS vUII ON vUII.UpdateId = vU.UpdateId --and State not in ('1','4','2')

       INNER JOIN PUBLIC_VIEWS.fnUpdateInstallationStateMap() AS fSM ON vUII.State = fSM.Id

       INNER JOIN PUBLIC_VIEWS.vComputerTarget AS vCT ON vUII.ComputerTargetId = vCT.ComputerTargetId

       INNER JOIN (SELECT DISTINCT

                     a.ComputerID,

                     b.Name as 'GroupName'

              FROM dbo.tbComputerTarget a, dbo.tbTargetGroup b, dbo.tbTargetInTargetGroup c

              WHERE c.TargetID = a.TargetID AND c.TargetGroupID = b.TargetGroupID) AS g ON g.ComputerID = vCT.ComputerTargetId

WHERE GroupName in ('WSUS_Example_Group_1','WSUS_Example_Group_2')

GROUP BY vCT.Name

       ,vCT.OSArchitecture

       ,vCT.IPAddress

       ,vCT.OSMajorVersion

       ,vCT.OSMinorVersion

       ,vCT.OSServicePackMajorNumber

       ,vCT.OSDefaultUILanguage

       ,vCT.Make

       ,vCT.Model

       ,g.GroupName

       ,vCT.LastSyncTime

       ,vCT.LastSyncResult

ORDER BY [Computer Name]