maravig

Sorry about that. I have the report running without errors, but I only get headers so I'm wondering if adding the collection at the end and how it's being joined (I tried left join and just join) is preventing any data from displaying?!? declare @asnid int = (select AssignmentID from fn_rbac_CIAssignment(@UserSIDs) where Assignment_UniqueID=@DEPLOYMENTID and AssignmentType in (1,5)) select s.ResourceID, m.Name0 as ComputerName0, m.User_Domain0+'\'+m.User_Name0 as LastLoggedOnUser, asite.SMS_Assigned_Sites0 as AssignedSite, m.Client_Version0 as ClientVersion, s.StateTime as DeploymentStateTime, (s.LastStatusMessageID&0x0000FFFF) as ErrorStatusID, sn.StateName as Status, a.Assignment_UniqueID as DeploymentID, statusinfo.MessageName as ErrorStatusName from fn_rbac_CIAssignment(@UserSIDs) a join ( select AssignmentID, ResourceID, StateType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentState_Combined(@UserSIDs) where @STATUS/10000 in (300,301) union all select AssignmentID, ResourceID, TopicType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentStatePerTopic(@UserSIDs) where @STATUS/10000 in (302) ) s on s.AssignmentID=a.AssignmentID and s.StateType=@STATUS/10000 and s.StateID = @STATUS%10000 left join fn_rbac_StateNames(@UserSIDs) sn on sn.TopicType=s.StateType and sn.StateID=isnull(s.StateID, 0) join fn_rbac_R_System(@UserSIDs) m on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)=0 left join fn_rbac_RA_System_SMSAssignedSites(@UserSIDs) asite on m.ResourceID = asite.ResourceID left join fn_rbac_AdvertisementStatusInformation(@UserSIDs) statusinfo on statusinfo.MessageID=nullif(s.LastStatusMessageID&0x0000FFFF, 0) left join v_FullCollectionMembership_Valid on v_FullCollectionMembership_Valid.resourceid = m.ResourceID where m.ResourceID in (select ResourceID from v_FullCollectionMembership_Valid where CollectionID = @CollectionID) and a.AssignmentID=@asnid order by m.Name0

I would like to add a prompt for collection to this report. I made a copy and added the CollectionID parameter prompt and what I thought was the right code, but I keep getting "Must declare the scalar variable '@CollectionID'. If I do declare it, I start syntax errors on a join. Has anyone ever attempted this before? Thanks, Jeff

I attached a screenshot of how it looks so far. I can't get the two columns from v_R_User to display. I've tried all combinations of Joins I can think of, but I'm missing something obvious. Here is my code: select v_R_System.Netbios_Name0 as [Computer Name], v_R_System.User_Name0 as [User Name], v_R_User.Full_User_Name0 as 'Full Name', v_R_User.manager0 as [Manager], v_GS_SoftwareFile.FileName, v_GS_SoftwareFile.FileVersion as [Internet Explorer Version], v_R_System.AD_Site_Name0 as [Site Code] FROM v_R_User FULL JOIN v_R_System ON v_R_User.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_SoftwareFile ON v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID INNER JOIN V_RA_SYSTEM_IPAddresses ON V_RA_SYSTEM_IPAddresses.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID WHERE FileName = 'iexplore.exe' Order by v_R_System.Netbios_Name0

Hello, I created a custom report with a collection prompt (SCCM 2012 r2) as seen here: select distinct v_R_System.Netbios_Name0, v_R_System.User_Name0, v_GS_SoftwareFile.FileName, v_GS_SoftwareFile.FileVersion from v_R_System join v_GS_SoftwareFile on v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId join v_FullCollectionMembership fcm on fcm.resourceid=v_r_system.resourceid WHERE fcm.Collectionid= @ColID and FileName = 'iexplore.exe' order by Netbios_Name0 Everything works, except I only get 1 machine as output! I originally didn't have a collection ID prompt, but it didn't make any difference. I did have to rebuild SCCM 3 weeks ago without the aid of any backup (long story). I turned on software inventory, turned on just about everything (especially SMS_Softwareinstalled, etc), used Right Click Tools, ran hardware and software inventory and still zip. Am I missing anything blatantly obvious? Thanks, Jeff