Hey everybody,

I am trying to merge two reports to yield what I anticipate will be an exceptionally large report. It is a combination of two of the default reports. The first is Software Updates - A Compliance>Compliance 7:

          declare @CI_ID int; select @CI_ID=CI_ID from fn_rbac_ConfigurationItems(@UserSIDs)  where CIType_ID=9 and CI_UniqueID=@AuthListID

          declare @StateID int
          select @StateID=StateID from fn_rbac_StateNames(@UserSIDs)  sn where sn.StateName=@StateName and TopicType=300

          select
            ccm.ResourceID,
            rs.Name0+isnull('.'+rs.Resource_Domain_or_Workgr0, '') as MachineName,
            rs.User_Domain0+'\'+User_Name0 as LastLoggedOnUser,
            asite.SMS_Assigned_Sites0 as AssignedSite,
            rs.Client_Version0 as ClientVersion
          from fn_rbac_ClientCollectionMembers(@UserSIDs)  ccm
          join fn_rbac_Update_ComplianceStatusAll(@UserSIDs)  cs on cs.CI_ID=@CI_ID and cs.ResourceID=ccm.ResourceID 
            and (@StateID=0 and cs.Status=0 or @StateID=1 and cs.Status in (1,3) or @StateID=2 and cs.Status=2)
          join fn_rbac_R_System(@UserSIDs)  rs on rs.ResourceID = ccm.ResourceID
          left join fn_rbac_RA_System_SMSAssignedSites(@UserSIDs)  asite on asite.ResourceID = ccm.ResourceID
          where ccm.CollectionID=@CollID
          order by MachineName

The second is Software Updates - A Compliance>Compliance 3:

          declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(@locale)
          select  
            Vendor=vnd.CategoryInstanceName, 
            UpdateClassification=cls.CategoryInstanceName,
            ui.Title,             
            ui.BulletinID, 
            ui.ArticleID, 
            Targeted=(case when ctm.ResourceID is not null then '*' else '' end), 
            Installed=(case when css.Status=3 then '*' else '' end), 
            IsRequired=(case when css.Status=2 then '*' else '' end), 
            Deadline=cdl.Deadline, 
            ui.CI_UniqueID as UniqueUpdateID, 
            ui.InfoURL as InformationURL 
          from fn_rbac_UpdateComplianceStatus(@UserSIDs)  css 
          join fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui on ui.CI_ID=css.CI_ID 
          join fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
          join fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
          left join fn_rbac_CITargetedMachines(@UserSIDs)  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
          outer apply (
            select Deadline=min(a.EnforcementDeadline) 
            from fn_rbac_CIAssignment(@UserSIDs)  a 
            join fn_rbac_CIAssignmentToCI(@UserSIDs)  atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
            ) cdl
          where css.ResourceID = @RscID
            and (@Vendor = '' or vnd.CategoryInstanceName = @Vendor) 
            and (@UpdateClass = '' or cls.CategoryInstanceName = @UpdateClass) 
          order by ui.Title

I want to preserve all the information from both reports, but have it output each individual patch for each machine in the collection.

Basically have the second report, but repeated for every machine in the collection.

have a look at the software update compliance reports from my blog .http://eskonr.com/?s=sccm+patch+reports you will find reports which you can edit based on your need.