SystemCenter Titans

select       CS.Name0,       CS.UserName0, case when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches')) else 'Good Client' end as 'Status',       ws.lasthwscan as 'Last HW scan',       FCM.collectionID--, from       v_UpdateComplianceStatus UCS left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.ResourceID join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid Where       UCS.Status = '2' and FCM.collectionid = 'SMS00001' Group by       CS.Name0,       CS.UserName0,

; WITH  folderHierarchy   ( CollectionID , [Name] , ParentCollectionID , [Path] ) AS (        SELECT             CollectionID ,             [Name] ,             ParentCollectionID ,              CAST ( '/' + [Name] + '/'   AS   VARCHAR ( MAX ))   AS  [Path]        FROM              ( SELECT                   CollectionID ,                   [Name] ,                   ParentCollectionID               FROM  v_Collection                    INNER   JOIN  v_CollectToSubCollect                    ON  v_Collection . CollectionID  =  v_CollectToSubCollect . SubCollectionID )   AS  V_1        WHERE             ParentCollectionID  =   'COLLROOT'      UNION   ALL     SELECT       child . CollectionID ,       child . Name ,       child . ParentCollectionID ,       parent . [Path] + child . [Name] + '/'   AS  [Path]     FROM        ( SELECT                   CollectionID ,                   [Name] ,