I wrote one that you can filter on deployment name. You don't need to declare
@UserSIDs in an SRS report, this is for SSMS. You should be able to tweak it to
get the expected result.
DECLARE @UserSIDs VARCHAR(100) = 'YOURSIDHERE'
SELECT
a.AssignmentName AS DeploymentName,
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 [Last State Received],
s.StateType,
sn.StateName AS Status,
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 StateType in (300,301)
) s ON s.AssignmentID=a.AssignmentID --and s.StateType=a.TopicType
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)
WHERE
a.AssignmentName LIKE '%TEST%' -- Deployment Name
ORDER BY
a.AssignmentName,
sn.StateName
From: [email protected]
To: [email protected]
Subject: [mssms] v_UpdateListStatus_Live
Date: Mon, 23 Sep 2013 14:30:58 +0000
Is there an equivalent to
v_UpdateListStatus_Live in CM12?
