OWSUG.ca

Select distinct

    SD.Name0 'Machine Name', 

    HARP.DisplayName0 as '32bit',

    HARP.Version0,  

    HARP64.DisplayName0 as '64bit',

    HARP64.Version0,

    SD.User_Name0 'User Name'  

From

    v_R_System SD 

    Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID 

    Join v_Collection COL on FCM.CollectionID = COL.CollectionID 

    left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on SD.ResourceID = HARP.ResourceID

    left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS_64 HARP64 on SD.ResourceID = HARP64.ResourceID

Where

     COL.Name = 'All Systems' 

    and (HARP.DisplayName0 = 'Configuration Manager Client' or HARP64.DisplayName0 = 'Configuration Manager Client')

Order By 

    SD.User_Name0