List computers by AV software

SELECT DISTINCT
    R.Netbios_Name0,
    R.User_Name0,
    WS.lastHWScan,
    CASE
        WHEN ARP.DisplayName0 = ‘Symantec Endpoint Protection’ THEN ‘*’
        else ‘ ‘
    END As SEP,
    CASE
        WHEN ARP.DisplayName0 = ‘McAfee VirusScan Enterprise’ THEN ‘*’
        else ‘ ‘
    END As Mcafee,
    ‘ ‘ As ‘No AVS’
FROM 
    dbo.v_R_System R
    left outer join dbo.v_RA_System_SMSAssignedSites RASS on (RASS.ResourceID = R.ResourceID)
    Left outer join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on (ARP.ResourceID = R.ResourceID)
    Left outer join dbo.v_GS_WORKSTATION_STATUS ws on (WS.ResourceID = R.ResourceID)
WHERE
    ARP.DisplayName0 = ‘Symantec Endpoint Protection’ OR ARP.DisplayName0 = ‘McAfee VirusScan Enterprise’

union

SELECT DISTINCT
    R.Netbios_Name0,
    R.User_Name0,
    WS.lastHWScan,
    ‘ ‘ As SEP,
    ‘ ‘ As Mcafee,
    ‘*’ As ‘No AVS’
FROM 
    dbo.v_R_System R
    left outer join dbo.v_RA_System_SMSAssignedSites RASS on (RASS.ResourceID = R.ResourceID)
    Left outer join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on (ARP.ResourceID = R.ResourceID)
    Left outer join dbo.v_GS_WORKSTATION_STATUS ws on (WS.ResourceID = R.ResourceID)
WHERE
    R.ResourceId not in (select ResourceID from dbo.v_GS_ADD_REMOVE_PROGRAMS ARP where ARP.DisplayName0 = ‘Symantec Endpoint Protection’ OR ARP.DisplayName0 = ‘McAfee VirusScan Enterprise’)
    AND R.Netbios_Name0 != ”
Order by
    R.Netbios_Name0,
    R.User_Name0