SELECT  DISTINCT
    a.Netbios_Name0 AS 'Computer Name',
    CASE WHEN  ARP.OMSA IS NULL THEN 'N/A' ELSE ARP.OMSA END AS 'Dell OMSA Version',
    CASE WHEN  ARP.Equallogic IS NULL THEN 'N/A' ELSE ARP.Equallogic END AS 'Broadcom Version'
 FROM 
    v_R_System_Valid a
    INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = a.ResourceID
    INNER JOIN v_FullCollectionMembership FCM on FCM.ResourceID = a.ResourceID
  
--INSTALLED SOFTWARE--
    LEFT OUTER JOIN (SELECT ResourceID, 
                MAX(CASE WHEN DisplayName0 ='Dell OpenManage Server Administrator' THEN Version0 ELSE 'Not Installed' END) AS 'OMSA',
                MAX(CASE WHEN DisplayName0 ='Dell Equallogic Host Integration Tools' THEN Version0 ELSE 'Not Installed' END) AS 'Equallogic'
            FROM 
                v_ADD_REMOVE_PROGRAMS 
            WHERE 
                    ProdID0 LIKE 'Microsoft SQL Server%' 
                    OR DisplayName0 = 'Dell OpenManage Server Administrator'
                    OR DisplayName0 = 'Dell Equallogic Host Integration Tools'
            GROUP BY 
                ResourceID) ARP ON ARP.ResourceID = a.ResourceID
 
 Where 
    FCM.CollectionID = 'SMS00001' 
 Order by 
    a.Netbios_Name0