Windows defender without System Center Endpoint Protection

Question

Hello,

I am trying to list the machine having Windows Defender feature but missing System Center Endpoint Protection Application:

select distinct sys.Name0, gsof.Name0,
CASE
WHEN gsof.InstallState0 = '1' THEN 'Enabled'
WHEN gsof.InstallState0 = '2' THEN 'Disabled'
WHEN gsof.InstallState0 = '3' THEN 'Absent'
ELSE 'Unknown'
END AS FeatureStatus
from v_R_System sys
Join v_FullCollectionMembership fcm on fcm.ResourceID = sys.ResourceID
inner join v_GS_OPTIONAL_FEATURE gsof on gsof.ResourceID = sys.ResourceId
where
gsof.Name0 = 'Windows-Defender' and gsof.InstallState0 = '1'
and
fcm.CollectionID = 'UCP00020'
and sys.ResourceID not exists (
Select v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID from v_GS_ADD_REMOVE_PROGRAMS_64
where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like '%System Center Endpoint Protection%')
and
sys.ResourceID not exists (
Select v_GS_ADD_REMOVE_PROGRAMS.ResourceID from v_GS_ADD_REMOVE_PROGRAMS
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%System Center Endpoint Protection%'
)
order by gsof.Name0

===========================================================================

I always got 0 result which is wrong as I know at least 2 machines with Windows Defender feature not having SCEP installed.

What is wrong?

Thanks,
Dom

Answer 1

there is no silver bullet, break down the query into its parts and review result and review the data on the devices.

Answer 2

Thanks Garth I found the issue it was a typo...