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