CREATE  view usd_v_csite as
select distinct CNT.domain_uuid as objectid, CNT.version_number+1 as version,
CNT.creation_user as creation_user, CNT.label as siteid, CNT.contact_information,
MAX(MC.manager_comp_version) as asmversion, CNT.creation_date as creationtime,
CNT.last_update_date as changetime, M.host_name as remoteaddress,
CNT.description as usercomment, M.host_uuid as uuid, CNT.domain_uuid
from ca_n_tier CNT, ca_manager M, ca_manager_component MC, ca_settings S
where S.set_id = 1 /* My own domain */
and CNT.domain_uuid <> S.set_val_uuid
and CNT.domain_type = 1   /* Domain = 1 */
and CNT.domain_uuid = M.domain_uuid
and CNT.domain_uuid = MC.domain_uuid
and (MC.manager_comp_id = 42
or MC.manager_comp_id = 43
or MC.manager_comp_id = 44
or MC.manager_comp_id = 45
or MC.manager_comp_id = 50)
group by CNT.domain_uuid, CNT.version_number,CNT.creation_user, CNT.label,
CNT.contact_information,CNT.creation_date,CNT.last_update_date,
M.host_name,CNT.description, M.host_uuid, CNT.domain_uuid
GO
GRANT SELECT ON  [dbo].[usd_v_csite] TO [ams_group]
GRANT SELECT ON  [dbo].[usd_v_csite] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_csite] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[usd_v_csite] TO [upmuser_group]
GO