將「適用於雲端的 Defender」資料新增至 Power BI

發行項
10/15/2024

透過將「適用於雲端的 Microsoft Defender」的資料與 Microsoft Power BI 連線，您可以輕鬆地監視和分析安全性指標。整合可讓您將安全性深入解析視覺化，並快速識別潛在的威脅和弱點。本文會引導您如何將「適用於雲端的 Defender」資料連線至 Power BI，協助您將複雜的安全性資訊轉換成清楚且可採取動作的深入解析。

必要條件

下載並安裝 Microsoft Power BI Desktop。
請確定您有正確的權限來存取 Azure Resource Graph。

將 Power BI 連接到 Azure Resource Graph

您必須先將 Power BI 連接到 Azure Resource Graph，才能將「適用於雲端的 Defender」的資料連接到 Power BI。

在您的桌面上開啟Power BI Desktop。
選取 [空白報表]。
選取 [取得資料]>[其他]。
搜尋並選取 [Azure Resource Graph]。
選取 Connect。

在 Power BI 中查詢「適用於雲端的 Defender」的資料

一旦 Power BI Desktop 連線到 Azure Resource Graph，您就可以使用 Azure Resource Graph 將各種資料來源從「適用於雲端的 Defender」查詢到 Power BI。

此頁面提供的查詢是提供結果的範例。 Azure Resource Graph 可讓您查詢各種不同的資料，您可以建立和自訂查詢，以傳回符合您特定需求的結果。

將其中一個提供的查詢複製並貼上到 Power BI Desktop 的查詢編輯器中。

此查詢會從 MDC 中擷取按照風險分類的安全性建議，使您能夠分析評估結果並識別需要關注的領域。

securityresources 
        | where type =~ "microsoft.security/assessments"
        | extend assessmentType = iff(type == "microsoft.security/assessments", tostring(properties.metadata.assessmentType), dynamic(null))
        | where (type == "microsoft.security/assessments" and (assessmentType in~ ("BuiltIn", "CustomerManaged")))
        | extend assessmentTypeSkimmed = iff(type == "microsoft.security/assessments", case(
                    tostring(properties.metadata.assessmentType) == "BuiltIn", "BuiltIn",
                    tostring(properties.metadata.assessmentType) == "BuiltInPolicy", "BuiltIn",
                    tostring(properties.metadata.assessmentType) == "CustomPolicy", "Custom",
                    tostring(properties.metadata.assessmentType) == "CustomerManaged", "Custom",
                    tostring(properties.metadata.assessmentType) == "ManualCustomPolicy", "Custom",
                    tostring(properties.metadata.assessmentType) == "ManualBuiltInPolicy", "BuiltIn",
                    dynamic(null)
                ), dynamic(null))
        | extend assessmentId = tolower(id)
        | extend assessmentKey = iff(type == "microsoft.security/assessments", name, dynamic(null))
        | extend source = iff(type == "microsoft.security/assessments", trim(' ', tolower(tostring(properties.resourceDetails.Source))), dynamic(null))
        | extend statusCode = iff(type == "microsoft.security/assessments", tostring(properties.status.code), dynamic(null))
        | extend resourceId = iff(type == "microsoft.security/assessments", trim(" ", tolower(tostring(case(source =~ "azure", properties.resourceDetails.Id,
            (type == "microsoft.security/assessments" and (source =~ "aws" and isnotempty(tostring(properties.resourceDetails.ConnectorId)))), properties.resourceDetails.Id,
            (type == "microsoft.security/assessments" and (source =~ "gcp" and isnotempty(tostring(properties.resourceDetails.ConnectorId)))), properties.resourceDetails.Id,
            source =~ "aws", properties.resourceDetails.AzureResourceId,
            source =~ "gcp", properties.resourceDetails.AzureResourceId,
            extract("^(?i)(.+)/providers/Microsoft.Security/assessments/.+$",1,id)
            )))), dynamic(null))
        | extend resourceName = iff(type == "microsoft.security/assessments", tostring(coalesce(properties.resourceDetails.ResourceName, properties.additionalData.CloudNativeResourceName, properties.additionalData.ResourceName, properties.additionalData.resourceName, split(resourceId, '/')[-1], extract(@"(.+)/(.+)", 2, resourceId))), dynamic(null))
        | extend resourceType = iff(type == "microsoft.security/assessments", tolower(properties.resourceDetails.ResourceType), dynamic(null))
        | extend riskLevelText = iff(type == "microsoft.security/assessments", tostring(properties.risk.level), dynamic(null))
        | extend riskLevel = iff(type == "microsoft.security/assessments", case(riskLevelText =~ "Critical", 4,
                  riskLevelText =~ "High", 3,
                  riskLevelText =~ "Medium", 2,
                  riskLevelText =~ "Low", 1,
                  0), dynamic(null))
        | extend riskFactors = iff(type == "microsoft.security/assessments", iff(isnull(properties.risk.riskFactors), dynamic([]), properties.risk.riskFactors), dynamic(null))
        | extend attackPaths = array_length(iff(type == "microsoft.security/assessments", iff(isnull(properties.risk.attackPathsReferences), dynamic([]), properties.risk.attackPathsReferences), dynamic(null)))           
        | extend displayName = iff(type == "microsoft.security/assessments", tostring(properties.displayName), dynamic(null))
        | extend statusCause = iff(type == "microsoft.security/assessments", tostring(properties.status.cause), dynamic(null))
        | extend isExempt = iff(type == "microsoft.security/assessments", iff(statusCause == "Exempt", tobool(1), tobool(0)), dynamic(null))
        | extend statusChangeDate = tostring(iff(type == "microsoft.security/assessments", todatetime(properties.status.statusChangeDate), dynamic(null)))
        | project assessmentId,
                    statusChangeDate,
                    isExempt,
                    riskLevel,
                    riskFactors,
                    attackPaths,
                    statusCode,
                    displayName,
                    resourceId,               
                    assessmentKey,
                    resourceType,
                    resourceName,
                    assessmentTypeSkimmed               
            | join kind=leftouter (
                securityresources
                | where type == 'microsoft.security/assessments/governanceassignments'
                | extend assignedResourceId = tolower(iff(type == "microsoft.security/assessments/governanceassignments", tostring(properties.assignedResourceId), dynamic(null)))
                | extend dueDate = iff(type == "microsoft.security/assessments/governanceassignments", todatetime(properties.remediationDueDate), dynamic(null))
                | extend owner = iff(type == "microsoft.security/assessments/governanceassignments", iff(isempty(tostring(properties.owner)), "unspecified", tostring(properties.owner)), dynamic(null))
                | extend governanceStatus = iff(type == "microsoft.security/assessments/governanceassignments", case(
                            isnull(todatetime(properties.remediationDueDate)), "NoDueDate",
                            todatetime(properties.remediationDueDate) >= bin(now(), 1d), "OnTime",
                            "Overdue"
                        ), dynamic(null))
                | project assignedResourceId, dueDate, owner, governanceStatus
            ) on $left.assessmentId == $right.assignedResourceId
            | extend completionStatusNumber = case(governanceStatus == "Overdue", 5,
                                                       governanceStatus == "OnTime", 4,
                                                       statusCode == "Unhealthy", 3, 
                                                       isExempt, 7,
                                                       1)
                | extend completionStatus = case(completionStatusNumber == 5, "Overdue",
                                                 completionStatusNumber == 4, "OnTime",
                                                 completionStatusNumber == 3, "Unassigned",
                                                 completionStatusNumber == 7, "Exempted",
                                                 "Completed")
            | where completionStatus in~ ("OnTime","Overdue","Unassigned")
            | project-away assignedResourceId, governanceStatus, isExempt
                       | order by riskLevel desc, attackPaths desc, displayName

使用此查詢來擷取攻擊路徑資料，讓您深入了解雲端環境內的潛在攻擊媒介。

securityresources
| where type == "microsoft.security/attackpaths"
| extend riskCategories = tostring(properties.riskCategories)
| extend riskCategories = tostring(split(riskCategories, "[")[1])
| extend riskCategories = tostring(split(riskCategories, "]")[0])
| extend riskCategory = iff('{riskCategories}' == "All", riskCategories, '{riskCategories}')
| where riskCategories has(riskCategory)
| project apId = name, apTemplate = tostring(properties.displayName), riskCategories
| summarize Path_Count = count() by Attack_Path = apTemplate, riskCategories
| project Attack_Path, Path_Count, riskCategories

此查詢會擷取安全分數資料，協助您了解整體安全性態勢，並排定補救工作的優先順序。

securityresources 
| where type == "microsoft.security/securescores" 
| where name == "ascScore"
| extend environment = tostring(properties.environment)
| extend scopeMaxScore = toint(properties.score.max)
| extend scopeWeight = toint(properties.weight)
| extend scopeScorePerc = round(todouble(properties.score.percentage), 0)

使用此查詢來取得治理規則的資料，讓您能夠有效地管理合規性和治理原則。

securityresources         
| where type == "microsoft.security/assessments"
| where isnull(properties.resourceDetails.AwsResourceId) and isnull(properties.resourceDetails.GcpResourceId)
| extend DisplayName = tostring(properties.displayName)
| where isempty(DisplayName) == false
| join kind=leftouter   (securityresources         
| where type == "microsoft.security/assessments/governanceassignments"
| extend  assignedResourceId = tostring(todynamic(properties).assignedResourceId)
| extend remediationDueDate = todatetime(properties.remediationDueDate)
| project id = assignedResourceId, governanceassignmentsProperties = todynamic(properties), remediationDueDate) on id
| extend hasAssignment = isempty( governanceassignmentsProperties) == false and isnull( governanceassignmentsProperties) == false
| extend assignmentStatus = iif(tostring(properties.status.code) == "Unhealthy",iif(hasAssignment == true, iif(bin(remediationDueDate, 1d) < bin(now(), 1d), "Overdue", "Ontime"), "Unassigned") , "Completed")
| summarize count() by assignmentStatus

此查詢會從 MDC 擷取合規性資料，這對於維護及示範遵守各種法規需求至關重要。

securityresources
| where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments" | extend scope = properties.scope
| where isempty(scope) or  scope in~("Subscription", "MultiCloudAggregation")
| parse id with * "regulatoryComplianceStandards/" complianceStandardId "/regulatoryComplianceControls/" complianceControlId "/regulatoryComplianceAssessments" *
| extend complianceStandardId = replace( "-", " ", complianceStandardId)
| extend Status = properties.state

選取 [確定]。

注意

根據預設，Resource Graph 會將所有查詢都限制為只傳回 1000 筆記錄。此控制項可保護您和服務，以防止會產生大型資料集的非預期查詢。如果您希望查詢結果不受 1000 條記錄限制的影響而截斷，請將「進階選項 - $resultTruncated (選用)」的值設為 FALSE。
選取載入。

有了 Azure Resource Graph，您即可彈性地擷取和分析「適用於雲端的 Defender」環境中可用的任何資料，以確保提供完整且量身定制的深入解析。將資料新增至 Power BI 之後，您可以建立視覺效果和儀表板，以有效地監視和管理安全性態勢。

後續步驟

在 Power BI 中新增視覺效果

共用方式為

將「適用於雲端的 Defender」資料新增至 Power BI

必要條件

將 Power BI 連接到 Azure Resource Graph

在 Power BI 中查詢「適用於雲端的 Defender」的資料

後續步驟

意見反應

其他資源