Defender for Endpointから脆弱性月次レポートを自動送信

Power Automateを使って、Defender for Endpoint（旧称：Defender ATP）の脆弱性月次レポートを毎月１日にメールにて自動配信させます。

高度なハンティングクエリを使って、メールに添付するレポートCSVファイルのクエリを記述します。

DeviceTvmSoftwareInventoryVulnerabilities  
| project  DeviceName, SoftwareName, CveId, SoftwareVersion, VulnerabilitySeverityLevel 
| join (DeviceTvmSoftwareVulnerabilitiesKB
| project AffectedSoftware, VulnerabilityDescription , CveId , CvssScore , IsExploitAvailable 
)
on CveId 
| project CveId , SoftwareName , SoftwareVersion , VulnerabilityDescription , VulnerabilitySeverityLevel, IsExploitAvailable , CvssScore 
| distinct SoftwareName , SoftwareVersion, CveId, VulnerabilityDescription , VulnerabilitySeverityLevel, IsExploitAvailable  
| sort by SoftwareName asc , SoftwareVersion

メール本文に掲載するレポートのクエリを記述します。

DeviceTvmSoftwareInventoryVulnerabilities  
| project DeviceName, SoftwareName, CveId, SoftwareVersion, VulnerabilitySeverityLevel
| join (DeviceTvmSoftwareVulnerabilitiesKB
| project AffectedSoftware, VulnerabilityDescription , CveId , CvssScore , IsExploitAvailable 
)
on CveId 
| project CveId , SoftwareName , SoftwareVersion , VulnerabilityDescription , VulnerabilitySeverityLevel, IsExploitAvailable , CvssScore 
| distinct SoftwareName , SoftwareVersion, CveId, VulnerabilityDescription , VulnerabilitySeverityLevel, IsExploitAvailable  
| summarize count() by VulnerabilitySeverityLevel
| sort by VulnerabilitySeverityLevel nulls last