create view ca_v_discovered_software as
select swdef.sw_def_uuid, swdef.name, swdef.software_type_id,
0 as software_format, 0 as vapp_props, NULL as virtual_package_name, NULL as virtual_package_uuid,
dissw.dis_sw_uuid, dissw.label, dissw.serial_number,
dissw.creation_user, dissw.creation_date, dissw.last_update_user, dissw.product_guid,
dissw.version_number, dissw.last_update_date, dissw.install_path, dissw.last_accessed,
dissw.asset_source_uuid, dissw.license_uuid, dissw.auto_rep_version,
dissw.exclude_registration, dissw.delete_time, dissw.is_managed_by_service,
dissw.is_ignored_by_vuln_impact, dissw.trustlevel, dissw.origin, dissw.def_source_type_id,
dissw.dis_source_type_id, dissw.license_id, dissw.license_instance, dissw.license_block_id,
dissw.covered_by_nf
from ca_discovered_software dissw, ca_software_def swdef
where dissw.sw_def_uuid=swdef.sw_def_uuid and swdef.software_type_id != 15
union (
select swdef.sw_def_uuid, swdef.name, swdef.software_type_id,
1 as software_format, disswprop.software_property_value as vapp_props,
swdef2.name as virtual_package_name, swdef2.sw_def_uuid as virtual_package_uuid,
dissw.dis_sw_uuid, dissw.label, dissw.serial_number,
dissw.creation_user, dissw.creation_date, dissw.last_update_user, dissw.product_guid,
dissw.version_number, dissw.last_update_date, dissw.install_path, dissw.last_accessed,
dissw.asset_source_uuid, dissw.license_uuid, dissw.auto_rep_version,
dissw.exclude_registration, dissw.delete_time, dissw.is_managed_by_service,
dissw.is_ignored_by_vuln_impact, dissw.trustlevel, dissw.origin, dissw.def_source_type_id,
dissw.dis_source_type_id, dissw.license_id, dissw.license_instance, dissw.license_block_id,
dissw.covered_by_nf
from ca_discovered_software dissw, ca_software_def swdef,
ca_link_sw_def vapplink, ca_software_def swdef2, ca_discovered_software_prop disswprop
where ( swdef2.software_type_id = 15
    and dissw.sw_def_uuid=vapplink.primary_sw_def_uuid
    and vapplink.link_type_id=9
    and vapplink.secondary_sw_def_uuid=swdef.sw_def_uuid
                and swdef2.sw_def_uuid = vapplink.primary_sw_def_uuid
                and dissw.dis_sw_uuid = disswprop.dis_sw_uuid
    and disswprop.software_property_name = 'vapp_props')
)
union (
select swdef.sw_def_uuid, swdef.name, swdef.software_type_id,
2 as software_format, disswprop.software_property_value as vapp_props,
NULL as virtual_package_name, NULL as virtual_package_uuid,
dissw.dis_sw_uuid, dissw.label, dissw.serial_number,
dissw.creation_user, dissw.creation_date, dissw.last_update_user, dissw.product_guid,
dissw.version_number, dissw.last_update_date, dissw.install_path, dissw.last_accessed,
dissw.asset_source_uuid, dissw.license_uuid, dissw.auto_rep_version,
dissw.exclude_registration, dissw.delete_time, dissw.is_managed_by_service,
dissw.is_ignored_by_vuln_impact, dissw.trustlevel, dissw.origin, dissw.def_source_type_id,
dissw.dis_source_type_id, dissw.license_id, dissw.license_instance, dissw.license_block_id,
dissw.covered_by_nf
from ca_discovered_software dissw, ca_software_def swdef,
     ca_discovered_software_prop disswprop
where swdef.software_type_id = 15 and
      dissw.sw_def_uuid=swdef.sw_def_uuid and
      (( dissw.dis_sw_uuid = disswprop.dis_sw_uuid and
       disswprop.software_property_name = 'vapp_props')
      )
)
GO
GRANT SELECT ON  [dbo].[ca_v_discovered_software] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_v_discovered_software] TO [ca_itrm_group_ams]
GO