SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW dbo.arg_discovered_hw_view
AS
SELECT         dh.dis_hw_uuid,
        dh.host_name AS discovery_asset_name,
        dh.serial_number AS dis_asset_serial_num,
        dh.primary_mac_address,
        dh.asset_tag,
        dh.label,
        dh.discovery_changes_switch,
        dh.domain_uuid,
        es.external_sys_name,
        es.discovery_id_1,
        es.discovery_id_2,
        es.discovery_id_3,
        es.discovery_id_4,
        es.discovery_id_5,
        es.discovery_changed_date,
        ds.asset_source_uuid,
        ds.subschema_id,
        oa.own_resource_uuid,
        oa.serial_number,
        oa.resource_alias,
        oa.resource_name,
        oa.host_name,
        i1.item_value_text AS sys_model,
        i2.item_value_text AS sys_type,
        i3.item_value_text AS sys_vendor,
        i4.item_value_text AS id_of_last_user,
        i5.item_value_double AS total_memory,
        i6.item_value_double AS total_disk_space,
        i7.item_value_text AS operating_sys,
        dh.asset_tag AS bios_asset_tag,
        CASE WHEN LTRIM(dh.primary_mac_address) is null OR DATALENGTH(LTRIM(dh.primary_mac_address)) = 0 THEN dh.host_name ELSE dh.primary_mac_address + ' ' + dh.host_name END AS mac_host        
        
FROM            ca_discovered_hardware dh

INNER JOIN    ca_discovered_hardware_ext_sys es ON es.dis_hw_uuid = dh.dis_hw_uuid
LEFT OUTER JOIN ca_asset_source ds ON ds.asset_source_uuid = dh.dis_hw_uuid and (ds.subschema_id > 9 and ds.subschema_id < 25) and ds.delete_time is null
LEFT OUTER JOIN ca_asset_source os ON os.logical_asset_uuid = ds.logical_asset_uuid and os.subschema_id = 1 and os.delete_time is null
LEFT OUTER JOIN    ca_owned_resource oa ON oa.asset_source_uuid = os.asset_source_uuid
LEFT OUTER JOIN inv_reconcile_item i1 ON i1.object_uuid = dh.dis_hw_uuid and  i1.item_parent_name_id = 2 and i1.item_name_id = 5
LEFT OUTER JOIN    inv_reconcile_item i2 ON i2.object_uuid = dh.dis_hw_uuid and  i2.item_parent_name_id = 2 and i2.item_name_id = 1
LEFT OUTER JOIN    inv_reconcile_item i3 ON i3.object_uuid = dh.dis_hw_uuid and  i3.item_parent_name_id = 2 and i3.item_name_id = 6
LEFT OUTER JOIN    inv_reconcile_item i4 ON i4.object_uuid = dh.dis_hw_uuid and  i4.item_parent_name_id = 39 and i4.item_name_id = 156
LEFT OUTER JOIN    inv_reconcile_item i5 ON i5.object_uuid = dh.dis_hw_uuid and  i5.item_parent_name_id = 2 and i5.item_name_id = 7
LEFT OUTER JOIN    inv_reconcile_item i6 ON i6.object_uuid = dh.dis_hw_uuid and  i6.item_parent_name_id = 31 and i6.item_name_id = 132
LEFT OUTER JOIN    inv_reconcile_item i7 ON i7.object_uuid = dh.dis_hw_uuid and  i7.item_parent_name_id = 5 and i7.item_name_id = 17

UNION ALL

SELECT         dh.dis_hw_uuid,
        dh.host_name AS discovery_asset_name,
        dh.serial_number AS dis_asset_serial_num,
        dh.primary_mac_address,
        dh.asset_tag,
        dh.label,
        dh.discovery_changes_switch,
        dh.domain_uuid,
        dl.dlvalue AS external_sys_name,
        ' ' AS discovery_id_1,
        ' ' AS discovery_id_2,
        ' ' AS discovery_id_3,
        ' ' AS discovery_id_4,
        ' ' AS discovery_id_5,
        ag.last_run_date AS discovery_changed_date,
        ds.asset_source_uuid,
        ds.subschema_id,
        oa.own_resource_uuid,
        oa.serial_number,
        oa.resource_alias,
        oa.resource_name,
        oa.host_name,
        i1.item_value_text AS sys_model,
        i2.item_value_text AS sys_type,
        i3.item_value_text AS sys_vendor,
        i4.item_value_text as id_of_last_user,
        i5.item_value_double AS total_memory,
        i6.item_value_double AS total_disk_space,
        i7.item_value_text AS operating_sys,
        dh.asset_tag AS bios_asset_tag,
        CASE WHEN LTRIM(dh.primary_mac_address) is null OR DATALENGTH(LTRIM(dh.primary_mac_address)) = 0 THEN dh.host_name ELSE dh.primary_mac_address + ' ' + dh.host_name END AS mac_host
        
FROM            ca_discovered_hardware dh

INNER JOIN    ca_agent ag ON ag.object_uuid = dh.dis_hw_uuid
INNER JOIN    arg_drpdnlst dl on dl.dlldid = 33000510 and dl.dlkey = 3
LEFT OUTER JOIN ca_asset_source ds ON ds.asset_source_uuid = dh.dis_hw_uuid and ds.subschema_id = 3 and ds.delete_time is null
LEFT OUTER JOIN ca_asset_source os ON os.logical_asset_uuid = ds.logical_asset_uuid and os.subschema_id = 1 and os.delete_time is null
LEFT OUTER JOIN    ca_owned_resource oa ON oa.asset_source_uuid = os.asset_source_uuid
LEFT OUTER JOIN inv_generalinventory_item i1 ON i1.object_uuid = dh.dis_hw_uuid and  i1.item_parent_name_id = 2 and i1.item_name_id = 5 and i1.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 2 and imax.item_name_id= 5)    
LEFT OUTER JOIN inv_generalinventory_item i2 ON i2.object_uuid = dh.dis_hw_uuid and  i2.item_parent_name_id = 2 and i2.item_name_id = 1 and i2.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 2 and imax.item_name_id = 1)
LEFT OUTER JOIN inv_generalinventory_item i3 ON i3.object_uuid = dh.dis_hw_uuid and  i3.item_parent_name_id = 2 and i3.item_name_id = 6 and i3.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 2 and imax.item_name_id = 6)
LEFT OUTER JOIN inv_generalinventory_item i4 ON i4.object_uuid = dh.dis_hw_uuid and  i4.item_parent_name_id = 39 and i4.item_name_id = 156 and i4.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 39 and imax.item_name_id = 156)
LEFT OUTER JOIN inv_generalinventory_item i5 ON i5.object_uuid = dh.dis_hw_uuid and  i5.item_parent_name_id = 2 and i5.item_name_id = 7 and i5.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 2 and imax.item_name_id = 7)
LEFT OUTER JOIN inv_generalinventory_item i6 ON i6.object_uuid = dh.dis_hw_uuid and  i6.item_parent_name_id = 31 and i6.item_name_id = 132 and i6.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 31 and imax.item_name_id = 132)
LEFT OUTER JOIN inv_generalinventory_item i7 ON i7.object_uuid = dh.dis_hw_uuid and  i7.item_parent_name_id = 5 and i7.item_name_id = 17 and i7.item_date=(select max(item_date) from inv_generalinventory_item imax where imax.object_uuid = dh.dis_hw_uuid and  imax.item_parent_name_id = 5 and imax.item_name_id = 17);


GO
GRANT SELECT ON  [dbo].[arg_discovered_hw_view] TO [swcmadmin]
GRANT SELECT ON  [dbo].[arg_discovered_hw_view] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[arg_discovered_hw_view] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[arg_discovered_hw_view] TO [uapmreporting_group]
GO