declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@AuthListID
SELECT     rs.Name0 AS MachineName, rs.User_Name0 AS LastLoggedOnUser, asite.SMS_Assigned_Sites0 AS AssignedSite, rs.Client_Version0 AS ClientVersion, 
                      (CASE cs.Status WHEN 0 THEN 'Compiance State Unknown' WHEN 1 THEN 'Compliant' WHEN 2 THEN 'Non-Compliant' WHEN 3 THEN 'Conflict Detected' ELSE 'Null' END)
                       AS State, cs.StatusTime
FROM         v_ClientCollectionMembers AS ccm INNER JOIN
                      v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = ccm.ResourceID INNER JOIN
                      v_R_System AS rs ON rs.ResourceID = ccm.ResourceID LEFT OUTER JOIN
                      v_RA_System_SMSAssignedSites AS asite ON asite.ResourceID = ccm.ResourceID
WHERE     (ccm.CollectionID = @CollID)
order by rs.Name0