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  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  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  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  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  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  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  

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