CREATE  
VIEW [dbo].[AL_COLLECTED_SYSTEMMODEL_VIEW]
AS
  SELECT m.model_uuid,
      c.company_uuid as manufacturer_uuid,
      c.company_name as manufacturer_name,
      m.name,
      m.authoritative,
      m.version_number
     FROM ca_model_def m
     INNER JOIN ca_company c
      ON m.manufacturer_uuid = c.company_uuid
     UNION ALL
     SELECT
      convert(binary(16), newid()) as model_uuid,
      convert(binary(16), newid())as manufacturer_uuid,
      manufacturer_name,
      name,
      3 as authoritative,
      0 as version_number
    FROM
     (Select DISTINCT      
      dh.vendor_name as manufacturer_name,
      ap.genmodel as name
       FROM ca_discovered_hardware dh
          INNER JOIN ca_agent_prop ap
            ON ap.object_uuid = dh.dis_hw_uuid
      WHERE dh.vendor_name is not null
      AND NOT EXISTS (select 1 from al_norm_system_model sm
      where sm.collected_manufacturer = dh.vendor_name
      and sm.collected_name = ap.genmodel)) as sub
GO