CREATE view usd_v_targets_os
(    agent_name, agent_type, agent_uuid,
    proc_os_name,
        is_boot_server, is_staging_server, is_user, is_computer,
    server_label, server_uuid, target_user_uuid
)
as

select     CA.agent_name, CA.agent_type, CA.object_uuid,
    CPO.proc_os_name,
                           
    case when (cast(substring(CDH.usage_list, 7, 1 ) as int) & 0x07) = 0x07
        then 1 else 0 end, /* boot server */         
        case when  (cast(substring(CDH.usage_list, 5, 1 ) as int) & 0x40) = 0x40

        then 1 else 0 end, /* SD Staging Server    */
        0, /* no user */
        1 /* computer */,
        CS.label as server_label, CS.server_uuid,
    ''  /* no user uuid */
from ca_agent CA, ca_proc_os CPO, ca_discovered_hardware CDH, ca_server CS, ca_agent_component CAC
where CA.agent_type=1            and
        CA.object_uuid=CDH.dis_hw_uuid    and
        CA.proc_os_id=CPO.proc_os_id     and
       (cast(substring(CDH.usage_list, 5, 1 ) as int) & 0x80) = 0x80
     /* SD Agent           */    and
        (CAC.agent_comp_id = 40 and CAC.object_uuid = CA.object_uuid and CS.server_uuid = CAC.server_uuid)
union

select     CA.agent_name, CA.agent_type, CA.object_uuid,
    CPO.proc_os_name,
    0, /* no boot server */         
    0, /* no SD Staging Server    */
        1, /* user */
        0 /* no computer */,
        CS.label as server_label, CS.server_uuid,
    CDU.user_uuid
from ca_agent CA, ca_proc_os CPO, ca_link_dis_hw_user CDU, ca_server CS, ca_agent_component CAC
where     CA.agent_type=4 and
        CA.object_uuid=CDU.link_dis_hw_user_uuid and
        CA.proc_os_id=CPO.proc_os_id and
                     
    (cast(substring(CDU.usage_list, 5, 1 ) as int) & 0x80) = 0x80

         /* SD Agent           */ and
        (CAC.agent_comp_id = 40 and CAC.object_uuid = CA.object_uuid and CS.server_uuid = CAC.server_uuid)
GO
GRANT SELECT ON  [dbo].[usd_v_targets_os] TO [ams_group]
GRANT REFERENCES ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[usd_v_targets_os] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[usd_v_targets_os] TO [upmuser_group]
GO