Quick Query to Find Devices without Forefront Endpoint Protection

The status of the endpoint protection agent is stored in SMS_G_System_EPDeploymentState SCCM database view but the key to the query is filtering on the DeploymentState field. The query below will return any machine that is not fully managed via Configuration Manager.

select SMS_R_System.Name, SMS_G_System_EPDeploymentState.DeploymentState, SMS_R_System.Active, SMS_R_System.ADSiteName, SMS_R_System.IPSubnets, SMS_R_System.IPAddresses, SMS_R_System.SystemOUName from SMS_R_System inner join SMS_G_System_EPDeploymentState on SMS_G_System_EPDeploymentState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_EPDeploymentState.DeploymentState != 3

What you need to understand is the different values of the DeploymentState column as the value 3 is the only true successful state. The values can be described as follows:

1 - Unmanaged
2 - To be Installed
3 - Managed (Success)
4 - Failed
5 - Reboot Pending

Happy reporting!