create view uapm_v_basic_hw_inv as
select d.host_name as System_Name,
            d.domain_uuid     Domain_uuid,
            d.dis_hw_uuid     System_uuid,
            d.serial_number     Serial_number,
            d.primary_mac_address     primary_mac_address,
            d.asset_tag Asset_Tag,
            d.asset_source_uuid asset_source_uuid,
            d.discovery_changes_switch discovery_changes_switch,
            d.external_host_key external_host_key,

            -- v1.value_text     System_Vendor,
            d.Vendor_Name     System_Vendor,
            v2.value_text System_Model,
            v3.value_text System_Type,
            v4.value_text Operating_System,
            v5.value_double Total_Memory,
            v6.value_double Total_Disk_Space,
            v14.value_double Domain_Name,
            v8.value_long No_of_Processors,
            v11.value_double Hardware_Scan,
            -- v12.value_text DNS_Name,
            d.primary_network_address DNS_Name,
            v13.value_text IP_Address
from (ca_discovered_hardware d -- left join uapm_v_invdata_l1 v1 on (v1.pparent_name='$40GeneralInventory$' and v1.parent_name='$System$' and v1.value_name='Vendor' and d.dis_hw_uuid=v1.hw_uuid )
            left join uapm_v_invdata_l1 v3 on (v3.pparent_name='$40GeneralInventory$' and v3.parent_name='$System$' and v3.value_name='Type' and d.dis_hw_uuid=v3.hw_uuid ))
            left join uapm_v_invdata_l1 v14 on (v14.pparent_name='$40GeneralInventory$' and v14.parent_name='$Network$' and v14.value_name='Domain Name' and d.dis_hw_uuid=v14.hw_uuid ),
            uapm_v_invdata_l1 v2,
            uapm_v_invdata_l1 v4,
            uapm_v_invdata_l1 v5,
            uapm_v_invdata_l1 v6,
            uapm_v_invdata_l1 v8,
            uapm_v_invdata_l1 v11,
            -- uapm_v_invdata_l1 v12,
            uapm_v_invdata_l1 v13 where  
            v2.pparent_name='$40GeneralInventory$' and v2.parent_name='$System$' and v2.value_name='Model'
and v4.pparent_name='$40GeneralInventory$' and v4.parent_name='$Operating System$' and v4.value_name='Operating System'
and v5.pparent_name='$40GeneralInventory$' and v5.parent_name='$System$' and v5.value_name='Total Memory'
and v6.pparent_name='$40GeneralInventory$' and v6.parent_name='$Storage$' and v6.value_name='Total Disk Space'
and v8.pparent_name='$40GeneralInventory$' and v8.parent_name='$System$' and v8.value_name='No. of Processors'
and v11.pparent_name='$40GeneralInventory$' and v11.parent_name='$System Status$' and v11.value_name='Hardware Scan'
-- and v12.pparent_name='$40GeneralInventory$' and v12.parent_name='$Network$' and v12.value_name='Computer Name'
and v13.pparent_name='$40GeneralInventory$' and v13.parent_name='$Network$' and v13.value_name='IP Address'
  and  d.dis_hw_uuid=v2.hw_uuid and d.dis_hw_uuid=v4.hw_uuid and d.dis_hw_uuid=v5.hw_uuid and d.dis_hw_uuid=v6.hw_uuid and d.dis_hw_uuid=v8.hw_uuid and d.dis_hw_uuid=v11.hw_uuid /* and d.dis_hw_uuid=v12.hw_uuid */ and d.dis_hw_uuid=v13.hw_uuid
and d.primary_network_address is not null
GO
GRANT SELECT ON  [dbo].[uapm_v_basic_hw_inv] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[uapm_v_basic_hw_inv] TO [ca_itrm_group_ams]
GO