CREATE VIEW coapi_agent_view_v3
AS
SELECT
dhw.label AS agv_label,
dhw.creation_user AS agv_creation_user,
dhw.creation_date AS agv_creation_date,
dhw.last_update_user AS agv_last_update_user,
dhw.last_update_date AS agv_last_update_date,
dhw.primary_network_address AS agv_network_address,
dhw.primary_mac_address AS agv_mac_address,
ag.object_uuid AS ag_object_uuid,
ag.domain_id AS ag_domain_id,
ag.server_uuid AS ag_server_uuid,
srv.host_name AS srv_host_name,
ag.boot_server_uuid AS ag_boot_server_uuid,
ag.unit_id AS ag_unit_id,
ag.last_run_date AS ag_last_run_date,
ag.agent_type AS ag_agent_type,
ag.agent_lock AS ag_agent_lock,
ag.agent_name AS ag_agent_name,
ag.agent_file_id AS ag_agent_file_id,
ag.agent_area AS ag_agent_area,
ag.agent_version AS ag_agent_version,
ag.description AS ag_description,
ag.prev_manager AS ag_prev_manager,
ag.ip_address AS ag_ip_address,
ag.registration_type AS ag_registration_type,
ag.proc_os_id AS ag_proc_os_id,
ag.class_id AS ag_class_id,
ag.origin AS ag_origin,
ag.trustlevel AS ag_trustlevel,
ag.derived_status_am AS ag_derived_status_am,
ag.derived_status_sd AS ag_derived_status_sd,
ag.derived_status_rc AS ag_derived_status_rc,
ag.derived_status_babld AS ag_derived_status_babld,
ag.derived_status_pt AS ag_derived_status_pt,
ag.derived_status_pt_av AS ag_derived_status_pt_av,
ag.derived_status_pt_as AS ag_derived_status_pt_as,
ag.derived_status_pt_ip AS ag_derived_status_pt_ip,
ag.user_def1 AS ag_user_def1,
ag.user_def2 AS ag_user_def2,
ag.user_def3 AS ag_user_def3,
ag.user_def4 AS ag_user_def4,
ag.creation_user AS ag_creation_user,
ag.creation_date AS ag_creation_date,
ag.last_update_date AS ag_last_update_date,
ag.last_update_user AS ag_last_update_user,
ag.version_number AS ag_version_number,
dom.label AS dom_label,
dhw.label AS dhw_label,
dhw.domain_uuid AS dhw_domain_uuid,
dhw.dis_hw_uuid AS dhw_dis_hw_uuid,
dhw.asset_source_uuid AS dhw_asset_source_uuid,
dhw.host_name AS dhw_host_name,
dhw.primary_network_address AS dhw_primary_network_address,
dhw.serial_number AS dhw_serial_number,
dhw.disc_serial_number AS dhw_disc_serial_number,
dhw.primary_mac_address AS dhw_primary_mac_address,
dhw.asset_tag AS dhw_asset_tag,
dhw.host_uuid AS dhw_host_uuid,
dhw.class_id AS dhw_class_id,
dhw.usage_list AS dhw_usage_list,
dhw.uri AS dhw_uri,
dhw.system_id AS dhw_system_id,
dhw.vendor_name AS dhw_vendor_name,
dhw.external_host_key AS dhw_external_host_key,
dhw.creation_user AS dhw_creation_user,
dhw.creation_date AS dhw_creation_date,
dhw.last_update_date AS dhw_last_update_date,
dhw.last_update_user AS dhw_last_update_user,
dhw.version_number AS dhw_version_number,
'' AS usr_label,
convert(binary(16),NULL) AS usr_domain_uuid,
convert(binary(16),NULL) AS usr_user_uuid,
'' AS usr_user_name,
convert(binary(32),0) AS usr_usage_list,
'' AS usr_uri,
'' AS usr_creation_user,
0 usr_creation_date,
0 usr_last_update_date,
'' AS usr_last_update_user,
0 usr_version_number,
'' AS cu_label,
convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
convert(binary(16),NULL) AS cu_dis_hw_uuid,
convert(binary(16),NULL) AS cu_user_uuid,
0 as cu_ref_count,
convert(binary(32),0) AS cu_usage_list,
'' AS cu_creation_user,
0 AS cu_creation_date,
'' AS cu_last_update_user,
0 as cu_last_update_date,
0 as cu_version_number,
'' AS exa_label,
convert(binary(16),NULL) AS exa_device_uuid,
convert(binary(16),NULL) AS exa_domain_uuid,
'' AS exa_class_name,
'' AS exa_ipaddress,
'' AS exa_macaddress,
'' AS exa_last_update_user,
0 AS exa_last_update_date,
0 AS exa_creation_date,
'' AS exa_creation_user,
0 AS exa_version_number,
grm.group_uuid as group_uuid
FROM ca_group_member grm, ca_discovered_hardware dhw, ca_n_tier dom, ca_agent AS ag LEFT OUTER JOIN ca_server AS srv ON ag.server_uuid = srv.server_uuid
WHERE (ag.object_uuid = dhw.dis_hw_uuid) AND (dhw.domain_uuid=dom.domain_uuid) AND (grm.member_uuid=ag.object_uuid)
UNION
SELECT
usr.label AS agv_label,
usr.creation_user AS agv_creation_user,
usr.creation_date AS agv_creation_date,
usr.last_update_user AS agv_last_update_user,
usr.last_update_date AS agv_last_update_date,
'' AS agv_network_address,
'' AS agv_mac_address,
ag.object_uuid AS ag_object_uuid,
ag.domain_id AS ag_domain_id,
ag.server_uuid AS ag_server_uuid,
srv.host_name AS srv_host_name,
ag.boot_server_uuid AS ag_boot_server_uuid,
ag.unit_id AS ag_unit_id,
ag.last_run_date AS ag_last_run_date,
ag.agent_type AS ag_agent_type,
ag.agent_lock AS ag_agent_lock,
ag.agent_name AS ag_agent_name,
ag.agent_file_id AS ag_agent_file_id,
ag.agent_area AS ag_agent_area,
ag.agent_version AS ag_agent_version,
ag.description AS ag_description,
ag.prev_manager AS ag_prev_manager,
ag.ip_address AS ag_ip_address,
ag.registration_type AS ag_registration_type,
ag.proc_os_id AS ag_proc_os_id,
ag.class_id AS ag_class_id,
ag.origin AS ag_origin,
ag.trustlevel AS ag_trustlevel,
ag.derived_status_am AS ag_derived_status_am,
ag.derived_status_sd AS ag_derived_status_sd,
ag.derived_status_rc AS ag_derived_status_rc,
ag.derived_status_babld AS ag_derived_status_babld,
ag.derived_status_pt AS ag_derived_status_pt,
ag.derived_status_pt_av AS ag_derived_status_pt_av,
ag.derived_status_pt_as AS ag_derived_status_pt_as,
ag.derived_status_pt_ip AS ag_derived_status_pt_ip,
ag.user_def1 AS ag_user_def1,
ag.user_def2 AS ag_user_def2,
ag.user_def3 AS ag_user_def3,
ag.user_def4 AS ag_user_def4,
ag.creation_user AS ag_creation_user,
ag.creation_date AS ag_creation_date,
ag.last_update_date AS ag_last_update_date,
ag.last_update_user AS ag_last_update_user,
ag.version_number AS ag_version_number,
dom.label AS dom_label,
'' AS dhw_label,
convert(binary(16),NULL) AS dhw_domain_uuid,
convert(binary(16),NULL) AS dhw_dis_hw_uuid,
convert(binary(16),NULL) AS dhw_asset_source_uuid,
'' AS dhw_host_name,
'' AS dhw_primary_network_address,
'' AS dhw_serial_number,
'' AS dhw_disc_serial_number,
'' AS dhw_primary_mac_address,
'' AS dhw_asset_tag,
'' AS dhw_host_uuid,
0 dhw_class_id,
convert(binary(32),0) AS dhw_usage_list,
'' AS dhw_uri,
'' AS dhw_system_id,
'' AS dhw_vendor_name,
'' AS dhw_external_host_key,
'' AS dhw_creation_user,
0 dhw_creation_date,
0 dhw_last_update_date,
'' AS dhw_last_update_user,
0 dhw_version_number,
usr.label AS usr_label,
usr.domain_uuid AS usr_domain_uuid,
usr.user_uuid AS usr_user_uuid,
usr.user_name AS usr_user_name,
usr.usage_list AS usr_usage_list,
usr.uri AS usr_uri,
usr.creation_user AS usr_creation_user,
usr.creation_date AS usr_creation_date,
usr.last_update_date AS usr_last_update_date,
usr.last_update_user AS usr_last_update_user,
usr.version_number AS usr_version_number,
'' AS cu_label,
convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
convert(binary(16),NULL) AS cu_dis_hw_uuid,
convert(binary(16),NULL) AS cu_user_uuid,
0 as cu_ref_count,
convert(binary(32),0) AS cu_usage_list,
'' AS cu_creation_user,
0 AS cu_creation_date,
'' AS cu_last_update_user,
0 as cu_last_update_date,
0 as cu_version_number,
'' AS exa_label,
convert(binary(16),NULL) AS exa_device_uuid,
convert(binary(16),NULL) AS exa_domain_uuid,
'' AS exa_class_name,
'' AS exa_ipaddress,
'' AS exa_macaddress,
'' AS exa_last_update_user,
0 AS exa_last_update_date,
0 AS exa_creation_date,
'' AS exa_creation_user,
0 AS exa_version_number,
grm.group_uuid as group_uuid
FROM  ca_group_member grm, ca_discovered_user usr, ca_n_tier dom, ca_agent AS ag LEFT OUTER JOIN ca_server AS srv ON ag.server_uuid = srv.server_uuid
WHERE (ag.object_uuid = usr.user_uuid) AND (usr.domain_uuid=dom.domain_uuid) AND (grm.member_uuid=ag.object_uuid)
UNION
SELECT
cu.label AS agv_label,
cu.creation_user AS agv_creation_user,
cu.creation_date AS agv_creation_date,
cu.last_update_user AS agv_last_update_user,
cu.last_update_date AS agv_last_update_date,
dhw.primary_network_address AS agv_network_address,
dhw.primary_mac_address AS agv_mac_address,
ag.object_uuid AS ag_object_uuid,
ag.domain_id AS ag_domain_id,
ag.server_uuid AS ag_server_uuid,
srv.host_name AS srv_host_name,
ag.boot_server_uuid AS ag_boot_server_uuid,
ag.unit_id AS ag_unit_id,
ag.last_run_date AS ag_last_run_date,
ag.agent_type AS ag_agent_type,
ag.agent_lock AS ag_agent_lock,
ag.agent_name AS ag_agent_name,
ag.agent_file_id AS ag_agent_file_id,
ag.agent_area AS ag_agent_area,
ag.agent_version AS ag_agent_version,
ag.description AS ag_description,
ag.prev_manager AS ag_prev_manager,
ag.ip_address AS ag_ip_address,
ag.registration_type AS ag_registration_type,
ag.proc_os_id AS ag_proc_os_id,
ag.class_id AS ag_class_id,
ag.origin AS ag_origin,
ag.trustlevel AS ag_trustlevel,
ag.derived_status_am AS ag_derived_status_am,
ag.derived_status_sd AS ag_derived_status_sd,
ag.derived_status_rc AS ag_derived_status_rc,
ag.derived_status_babld AS ag_derived_status_babld,
ag.derived_status_pt AS ag_derived_status_pt,
ag.derived_status_pt_av AS ag_derived_status_pt_av,
ag.derived_status_pt_as AS ag_derived_status_pt_as,
ag.derived_status_pt_ip AS ag_derived_status_pt_ip,
ag.user_def1 AS ag_user_def1,
ag.user_def2 AS ag_user_def2,
ag.user_def3 AS ag_user_def3,
ag.user_def4 AS ag_user_def4,
ag.creation_user AS ag_creation_user,
ag.creation_date AS ag_creation_date,
ag.last_update_date AS ag_last_update_date,
ag.last_update_user AS ag_last_update_user,
ag.version_number AS ag_version_number,
dom.label AS dom_label,
dhw.label AS dhw_label,
dhw.domain_uuid AS dhw_domain_uuid,
dhw.dis_hw_uuid AS dhw_dis_hw_uuid,
dhw.asset_source_uuid AS dhw_asset_source_uuid,
dhw.host_name AS dhw_host_name,
dhw.primary_network_address AS dhw_primary_network_address,
dhw.serial_number AS dhw_serial_number,
dhw.disc_serial_number AS dhw_disc_serial_number,
dhw.primary_mac_address AS dhw_primary_mac_address,
dhw.asset_tag AS dhw_asset_tag,
dhw.host_uuid AS dhw_host_uuid,
dhw.class_id AS dhw_class_id,
dhw.usage_list AS dhw_usage_list,
dhw.uri AS dhw_uri,
dhw.system_id AS dhw_system_id,
dhw.vendor_name AS dhw_vendor_name,
dhw.external_host_key AS dhw_external_host_key,
dhw.creation_user AS dhw_creation_user,
dhw.creation_date AS dhw_creation_date,
dhw.last_update_date AS dhw_last_update_date,
dhw.last_update_user AS dhw_last_update_user,
dhw.version_number AS dhw_version_number,
'' AS usr_label,
convert(binary(16),NULL) AS usr_domain_uuid,
convert(binary(16),NULL) AS usr_user_uuid,
'' AS usr_user_name,
convert(binary(32),0) AS usr_usage_list,
'' AS usr_uri,
'' AS usr_creation_user,
0 usr_creation_date,
0 usr_last_update_date,
'' AS usr_last_update_user,
0 usr_version_number,
cu.label AS cu_label,
cu.link_dis_hw_user_uuid AS cu_link_dis_hw_user_uuid,
cu.dis_hw_uuid AS cu_dis_hw_uuid,
cu.user_uuid AS cu_user_uuid,
cu.ref_count AS cu_ref_count,
cu.usage_list AS cu_usage_list,
cu.creation_user AS cu_creation_user,
cu.creation_date AS cu_creation_date,
cu.last_update_user AS cu_last_update_user,
cu.last_update_date AS cu_last_update_date,
cu.version_number AS cu_version_number,
'' AS exa_label,
convert(binary(16),NULL) AS exa_device_uuid,
convert(binary(16),NULL) AS exa_domain_uuid,
'' AS exa_class_name,
'' AS exa_ipaddress,
'' AS exa_macaddress,
'' AS exa_last_update_user,
0 AS exa_last_update_date,
0 AS exa_creation_date,
'' AS exa_creation_user,
0 AS exa_version_number,
grm.group_uuid as group_uuid
FROM ca_group_member grm, ca_link_dis_hw_user cu, ca_discovered_hardware dhw, ca_n_tier dom, ca_agent AS ag LEFT OUTER JOIN ca_server AS srv ON ag.server_uuid = srv.server_uuid
WHERE (ag.object_uuid = cu.link_dis_hw_user_uuid) AND
(cu.dis_hw_uuid=dhw.dis_hw_uuid) AND
(dhw.domain_uuid=dom.domain_uuid) AND (grm.member_uuid=ag.object_uuid)
UNION
SELECT
exa.label  COLLATE Latin1_General_CI_AS AS agv_label,
exa.creation_user AS agv_creation_user,
exa.creation_date AS agv_creation_date,
exa.last_update_user AS agv_last_update_user,
exa.last_update_date AS agv_last_update_date,
exa.ipaddress  COLLATE Latin1_General_CI_AS AS agv_network_address,
exa.macaddress  COLLATE Latin1_General_CI_AS AS agv_mac_address,
ag.object_uuid AS ag_object_uuid,
ag.domain_id AS ag_domain_id,
ag.server_uuid AS ag_server_uuid,
srv.host_name AS srv_host_name,
ag.boot_server_uuid AS ag_boot_server_uuid,
ag.unit_id AS ag_unit_id,
ag.last_run_date AS ag_last_run_date,
ag.agent_type AS ag_agent_type,
ag.agent_lock AS ag_agent_lock,
ag.agent_name AS ag_agent_name,
ag.agent_file_id AS ag_agent_file_id,
ag.agent_area AS ag_agent_area,
ag.agent_version AS ag_agent_version,
ag.description AS ag_description,
ag.prev_manager AS ag_prev_manager,
ag.ip_address AS ag_ip_address,
ag.registration_type AS ag_registration_type,
ag.proc_os_id AS ag_proc_os_id,
ag.class_id AS ag_class_id,
ag.origin AS ag_origin,
ag.trustlevel AS ag_trustlevel,
ag.derived_status_am AS ag_derived_status_am,
ag.derived_status_sd AS ag_derived_status_sd,
ag.derived_status_rc AS ag_derived_status_rc,
ag.derived_status_babld AS ag_derived_status_babld,
ag.derived_status_pt AS ag_derived_status_pt,
ag.derived_status_pt_av AS ag_derived_status_pt_av,
ag.derived_status_pt_as AS ag_derived_status_pt_as,
ag.derived_status_pt_ip AS ag_derived_status_pt_ip,
ag.user_def1 AS ag_user_def1,
ag.user_def2 AS ag_user_def2,
ag.user_def3 AS ag_user_def3,
ag.user_def4 AS ag_user_def4,
ag.creation_user AS ag_creation_user,
ag.creation_date AS ag_creation_date,
ag.last_update_date AS ag_last_update_date,
ag.last_update_user AS ag_last_update_user,
ag.version_number AS ag_version_number,
dom.label AS dom_label,
'' AS dhw_label,
convert(binary(16),NULL) AS dhw_domain_uuid,
convert(binary(16),NULL) AS dhw_dis_hw_uuid,
convert(binary(16),NULL) AS dhw_asset_source_uuid,
'' AS dhw_host_name,
'' AS dhw_primary_network_address,
'' AS dhw_serial_number,
'' AS dhw_disc_serial_number,
'' AS dhw_primary_mac_address,
'' AS dhw_asset_tag,
'' AS dhw_host_uuid,
0 dhw_class_id,
convert(binary(32),0) AS dhw_usage_list,
'' AS dhw_uri,
'' AS dhw_system_id,
'' AS dhw_vendor_name,
'' AS dhw_external_host_key,
'' AS dhw_creation_user,
0 dhw_creation_date,
0 dhw_last_update_date,
'' AS dhw_last_update_user,
0 dhw_version_number,
'' AS usr_label,
convert(binary(16),NULL) AS usr_domain_uuid,
convert(binary(16),NULL) AS usr_user_uuid,
'' AS usr_user_name,
convert(binary(32),0) AS usr_usage_list,
'' AS usr_uri,
'' AS usr_creation_user,
0 usr_creation_date,
0 usr_last_update_date,
'' AS usr_last_update_user,
0 usr_version_number,
'' AS cu_label,
convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
convert(binary(16),NULL) AS cu_dis_hw_uuid,
convert(binary(16),NULL) AS cu_user_uuid,
0 as cu_ref_count,
convert(binary(32),0) AS cu_usage_list,
'' AS cu_creation_user,
0 AS cu_creation_date,
'' AS cu_last_update_user,
0 as cu_last_update_date,
0 as cu_version_number,
exa.label AS exa_label,
exa.device_uuid AS exa_device_uuid,
exa.domain_uuid AS exa_domain_uuid,
exa.class_name AS exa_class_name,
exa.ipaddress AS exa_ipaddress,
exa.macaddress AS exa_macaddress,
exa.last_update_user AS exa_last_update_user,
exa.last_update_date AS exa_last_update_date,
exa.creation_date AS exa_creation_date,
exa.creation_user AS exa_creation_user,
exa.version_number AS exa_version_number,
grm.group_uuid as group_uuid
FROM ca_group_member grm, am_external_device exa, ca_n_tier dom, ca_agent AS ag LEFT OUTER JOIN ca_server AS srv ON ag.server_uuid = srv.server_uuid
WHERE (ag.object_uuid = exa.device_uuid) AND (exa.domain_uuid=dom.domain_uuid) AND (grm.member_uuid=ag.object_uuid)
GO
GRANT SELECT ON  [dbo].[coapi_agent_view_v3] TO [ams_group]
GRANT SELECT ON  [dbo].[coapi_agent_view_v3] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[coapi_agent_view_v3] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[coapi_agent_view_v3] TO [upmuser_group]
GO