Ever got into knowing what are the basis at which the Dashboard calculates the EP status as "At Risk"? If yes then this can help elaborate to get some more details on why the machines were put into At Risk state.

To be sure what exactly I am talking about. Here is the snapshot. It helps decodes Computer Status, Deployment State, Policy Application state in friendly names.

 

 

 

SQL
Edit|Remove
SELECT SD.Name0 as 'Machine Name'CASE 
    When EPA.ComputerStatus = 0 THEN 'Unknown' 
    When EPA.ComputerStatus = 1 THEN 'None' 
    When EPA.ComputerStatus = 2 THEN 'Cleaned' 
    When EPA.ComputerStatus = 3 THEN 'Pending' 
    When EPA.ComputerStatus = 4 THEN 'Failed' 
    End 
    AS [Computer Status], 
CASE 
    When EPD.DeploymentState = 1 THEN 'UnManaged by SCCM' 
    When EPD.DeploymentState = 3 THEN 'To be Installed' 
    When EPD.DeploymentState = 3 THEN 'Installed and Managed by SCCM' 
    When EPD.DeploymentState = 4 THEN 'Install Failed ' 
    When EPD.DeploymentState=  5 THEN 'Reboot Pending' 
    End 
    AS [EP Deployment State], 
CASE   
    When EPP.PolicyApplicationState = 1 THEN 'Success' 
    When EPP.PolicyApplicationState = 2 THEN 'Failed' 
    End 
    AS [EP Policy Application State], 
    VSS.EpEnabledVSS.HealthyVSS.EpManaged,EHS.AntivirusSignatureAge 
FROM vSMS_G_System_EndpointProtectionStatus vss 
INNER JOIN system_disc sd ON vss.resourceid=sd.itemkey 
INNER JOIN EP_AntimalwareInfectionStatus EPA ON vss.ResourceIDEPA.MachineID 
INNER JOIN EP_DeploymentState EPD ON vss.ResourceID=EPD.MachineID 
INNER JOIN EP_PolicyApplicationState EPP ON vss.ResourceID=EPP.MachineID 
INNER JOIN EP_AntimalwareHealthStatus EHS ON vss.ResourceID=EHS.MachineID 
WHERE VSS.EpAtRisk=1  
--Add “and EHS.AntivirusSignatureAge > 7” towards the end of the query if you want to get machines older than certain number of days.