CREATE view usd_v_agents
(
    name,
    object_uuid,
    agent_name,
    agent_type
)
as
    select dhw.host_name as name,
        dhw.dis_hw_uuid as object_uuid,
        dhw.label as agent_name,
        a.agent_type  as agent_type
    from  ca_discovered_hardware dhw , ca_agent a
    where a.object_uuid=dhw.dis_hw_uuid and a.agent_type=1
union
    select dusr.user_name as name,
        dusr.user_uuid as object_uuid,
        dusr.label as agent_name,
        a.agent_type
    from ca_discovered_user dusr , ca_agent a
    where a.object_uuid=dusr.user_uuid and a.agent_type=4
GO
GRANT SELECT ON  [dbo].[usd_v_agents] TO [ams_group]
GRANT REFERENCES ON  [dbo].[usd_v_agents] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_agents] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[usd_v_agents] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[usd_v_agents] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[usd_v_agents] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_agents] TO [ca_itrm_group_ams]
GO