Le script SQL ci-dessous propose de lister les détails de l'état et la configuration des antivirus Defender pour des machines gérés par SCCM.
/****** ALL WORKSTATIONS ENDPOINT PROTECTION DETAILS ******/
SELECT DISTINCT (S.ResourceID)
,S.Name0 AS 'Machine Name'
,AD_Site_Name0 AS 'AD Site'
,S.Operating_System_Name_and0 AS 'Operating System'
,CASE [EpProtected]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Protected'
,CASE [EpAtRisk]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Computer at Risk'
,CASE [EpNotYetInstalled]
WHEN 1 THEN 'NOT INSTALLED'
WHEN 0 THEN 'INSTALLED'
ELSE 'UNKNOWN'
END AS 'Endpoint Installed'
,CASE [EpUnsupported]
WHEN 1 THEN 'UNSUPPORTED'
WHEN 0 THEN 'SUPPORTED'
ELSE 'UNKNOWN'
END AS 'Endpoint Support'
,CASE[Inactive]
WHEN 1 THEN 'INACTIVE'
WHEN 0 THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS 'SCCM Client Activity'
,CASE[NotClient]
WHEN 1 THEN 'NO'
WHEN 0 THEN 'YES'
ELSE 'UNKNOWN'
END AS 'SCCM Client'
,CASE [AmRemediationFailed]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Failed Remediation'
,CASE [AmFullscanRequired]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Full Scan Required'
,CASE [AmRestartRequired]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Restart Required'
,CASE [AmOfflineScanRequired]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Offline Scan Required'
,CASE [AmManualStepsRequired]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Manual Scan Required'
,CASE [AmRecentlyCleaned]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Recently Cleaned'
,CASE [AmThreatActivity]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Threat Activity'
,CASE [EpInstallFailed]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Failed Install'
,CASE [EpEnforcementSucceeded]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Enforce Succeed'
,CASE [EpEnforcementFailed]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Enforce Failed'
,CASE [EpPendingReboot]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Pending Reboot'
,CASE [Unhealthy]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Unhealthy'
,CASE [SignatureUpTo1DayOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Signature Age 1 day old'
,CASE [SignatureUpTo3DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Signature Age 3 day old'
,CASE [SignatureUpTo7DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Signature Age 7 day old'
,CASE [SignatureOlderThan7Days]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Signature Age over 7 day old'
,CASE [NoSignature]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'No Signature'
,CASE [AmPending]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware Pending'
,CASE [LastScanUpTo2DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Last Scan 2 days old'
,CASE [LastScanUpTo8DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Last Scan 8 days old'
,CASE [LastScanUpTo31DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Last Scan 31 days old'
,CASE [LastScanOver31DaysOld]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Last Scan Over 31 days old'
,CASE [Healthy]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Healthy'
,CASE [Active]
WHEN 1 THEN 'ACTIVE'
WHEN 0 THEN 'INACTIVE'
ELSE 'UNKNOWN'
END AS 'Client Activity'
,CASE [EpUnmanaged]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Not Managed'
,CASE [EpToBeInstalled]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint To be installed'
,CASE [EpManaged]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Managed'
,CASE [EpInstalled]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Managed'
,CASE [EpEnforced]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Enforced'
,CASE [EpEnabled]
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Endpoint Enabled'
,CASE AMSH.Enabled
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'AntiMalware_Enabled'
,AMSH.Version as AntiMalware_Version
--,AMSH.ProductStatus
,CASE AMSH.RtpEnabled
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'RealTime_Ptct_Enabled'
,CASE AMSH.OnAccessProtectionEnabled -- Specifies whether the computer is monitoring file and program activity on your computer
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'OnAccess_Ptct_Enabled'
,CASE AMSH.IoavProtectionEnabled -- Scan all downloaded files and attachments
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Downloaded_Ptct_Enabled'
,CASE AMSH.BehaviorMonitorEnabled
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Behavior_Monitor_Enabled'
,CASE AMSH.AntivirusEnabled
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Antivirus_Enabled'
,CASE AMSH.AntispywareEnabled
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
ELSE 'UNKNOWN'
END AS 'Antispyware_Enabled'
,AMSH.EngineVersion
,AMSH.LastQuickScanDateTimeStart as 'Last QuickScan DateTime Start'
,AMSH.LastQuickScanDateTimeEnd as 'Last QuickScan DateTime End'
,AMSH.LastFullScanDateTimeStart as 'Last FullScan DateTime Start'
,AMSH.LastFullScanDateTimeEnd as 'Last FullScan DateTime End'
,AMSH.LastFullScanAge as 'Last FullScan Age'
,AMSH.LastQuickScanAge as 'Last Quick Scan Age'
,AMSH.AntivirusSignatureUpdateDateTime as 'Antivirus Signature Update DateTime'
,AMSH.AntiSpywareSignatureUpdateDateTime as 'AntiSpyware Signature Update DateTime'
,AMSH.AntivirusSignatureAge as 'Antivirus Signature Age'
,AMSH.AntispywareSignatureAge as 'Antispyware Signature Age'
,AMSH.AntivirusSignatureVersion as 'Antivirus Signature Version'
,AMSH.AntispywareSignatureVersion as 'Anti spyware Signature Version'
FROM [CM_BIM].[dbo].[v_EndpointProtectionStatus] EPPS /*(v_EndpointProtectionStatus: Fournit un résumé de l'état des clients Endpoint Protection global pour chaque ordinateur)*/
INNER JOIN v_R_System S on S.ResourceID = EPPS.ResourceID
INNER JOIN v_GS_AntimalwareHealthStatus AMSH on AMSH.ResourceID = EPPS.ResourceID /*(v_GS_AntimalwareHealthStatus: Most recent snapshot of the AntimalwareHealthStatus WMI class for each client where EndPoint Protection is installed)*/