CREATE  
VIEW [dbo].[AL_COLLECTED_OS_VIEW]
AS
  SELECT
    id,
    name,
    authoritative,
    version_number
  FROM ca_resource_operating_system os
  UNION ALL
  SELECT DISTINCT TOP 100 PERCENT
    checksum(newid()) as id,
    sub.name,
    3 as authoritative,
    0 as version_number
   FROM
     (Select Distinct
            ap.GenOS as name
       FROM ca_discovered_hardware dh INNER JOIN ca_agent_prop ap
            ON ap.object_uuid = dh.dis_hw_uuid
        WHERE
         ap.GenOS is not null
         AND NOT EXISTS (select 1 from al_norm_os no
           where no.collected_value = ap.GenOS)) as sub
GO