Jump to content


Computers in a specific state for a deployment with collection prompt?

Recommended Posts

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?


Share this post

Link to post
Share on other sites

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))
            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


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...