Add Defender for Cloud data to Power BI
By connecting Microsoft Defender for Cloud's data with Power BI, you can easily monitor and analyze your security metrics. The integration allows you to visualize security insights and quickly identify potential threats and vulnerabilities. This article guides you through the steps to connect Defender for Cloud data to Power BI, helping you transform complex security information into clear, actionable insights.
Prerequisites
Ensure you have the correct permissions to access Azure Resource Graph.
Connect Power BI to Azure Resource Graph
Before you can connect Defender for Cloud's data to Power BI, you must first connect Power BI to Azure Resource Graph.
On your desktop open Power BI Desktop.
Select Blank report.
Select Get data > more.
Search for and select Azure Resource Graph.
Select Connect.
Query Defender for Cloud data in to Power BI
Once Power BI Desktop is connected to Azure Resource Graph, you can use Azure Resource Graph to query various data sources from Defender for Cloud into Power BI.
The queries provided on this page are examples that provide results. Azure Resource Graph allows you to query a wide range of data that you can create and customize to return results that suit your specific requirements.
Copy and paste one of the provided queries into the query editor in Power BI Desktop.
This query retrieves security recommendations by risk from MDC, allowing you to analyze assessments and identify areas that need attention.
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
Select Ok.
Note
By default, Resource Graph limits any query to returning only 1000 records. This control protects both you and the service from unintentional queries that would result in large data sets. If you want query results not to be truncated by the 1000 records limit, set the value of the "Advanced Option - $resultTruncated (optional)" to FALSE.
Select Load.
With Azure Resource Graph, you have the flexibility to retrieve and analyze any data available within your Defender for Cloud environment, ensuring comprehensive and tailored insights. Once your data is added to Power BI, you can create visualizations and dashboards to monitor and manage your security posture effectively.