SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW dbo.arg_web_discovered_asset_view
AS
SELECT             rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            dh.serial_number AS discovery_asset_serial_num,
            dh.host_name AS discovery_asset_name,
            i1.item_value_text AS sys_vendor,
            i2.item_value_text AS sys_model
                
FROM                 arg_reconcile_data rd

INNER JOIN         ca_discovered_hardware dh ON rd.dis_hw_uuid = dh.dis_hw_uuid
INNER JOIN        ca_discovered_hardware_ext_sys es ON es.dis_hw_uuid = dh.dis_hw_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 = 6
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 = 5
                
WHERE             (rd.reconcile_data_type = 4)


UNION ALL

SELECT             rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            dh.serial_number AS discovery_asset_serial_num,
            dh.host_name AS discovery_asset_name,
            i1.item_value_text AS sys_vendor,
            i2.item_value_text AS sys_model
                
FROM                 arg_reconcile_data rd

INNER JOIN         ca_discovered_hardware dh ON rd.dis_hw_uuid = dh.dis_hw_uuid
INNER JOIN        ca_agent ag ON ag.object_uuid = dh.dis_hw_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 = 6
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 = 5
                
WHERE             (rd.reconcile_data_type = 4)

GO
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [swcmadmin]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmadmin]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmbatch]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmreporting]
GRANT SELECT ON  [dbo].[arg_web_discovered_asset_view] TO [uapmreporting_group]
GO