As cloud environments grow more complex and threats increase, organizations need robust tools to monitor, analyze, and respond to security issues effectively. Microsoft Defender for Cloud (MDC) offers robust security management, but to unlock its full potential, organizations need powerful visualization and analysis tools.
While Azure Workbooks provide valuable visualizations for MDC data, integrating Microsoft Power BI offers an enhanced approach to data analysis and visualization. Power BI's advanced features, such as customizable dashboards, interactive elements, and seamless integration with various data sources, make it ideal for enhancing the value derived from MDC data.
This article is the first in a series of correlated blogs that will explore scenarios and applicability in depth. As an introduction to the series, this article provides the foundation on how to start leveraging Power BI to report and dashboard MDC insights.
To integrate MDC data into Power BI, follow these steps:
Step 1: Set Up Power BI and Azure Resource Graph
Step 2: Connect Power BI to Azure Resource Graph
Step 3: Load MDC Data into Power BI
Once you've connected Power BI to Azure Resource Graph, you can begin loading MDC data.
Here, we'll provide a few example queries to retrieve data for recommendations, attack paths, secure scores, and governance. Note that these are just a few examples; you can retrieve any data available in Azure Resource Graph (ARG) according to your needs.
NOTE: By default, Resource Graph limits any query to returning only 1000 records. This control protects both the user 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.
Use the following ARG queries to pull the main MDC data points:
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
Use this query to fetch attack path data, providing insights into potential attack vectors within your cloud environment.
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
This query retrieves secure score data, helping you understand your overall security posture and prioritize remediation efforts.
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)
Use this query to get data on governance rules, enabling you to manage compliance and governance policies effectively.
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
This query retrieves compliance data from MDC, which is essential for maintaining and demonstrating adherence to various regulatory requirements.
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
Remember, the queries provided above are just examples. ARG allows you to query a wide range of data, so feel free to customize and create queries that suit your specific requirements. With ARG, you have the flexibility to retrieve and analyze any data available within your MDC environment, ensuring comprehensive and tailored insights.
Step 4: Create Visualizations in Power BI
Perhaps you can build a report similar to the one shown in the picture below.
If you prefer, you can also use a predefined sample report available for download from the Defender for Cloud GitHub.
This sample report provides a great starting point and can be customized further to meet your specific needs, ensuring you get the most out of your MDC data.
Step 5: Share and Collaborate
By leveraging Power BI's advanced features alongside Azure Workbooks, organizations can unlock deeper insights, create more customized and interactive reports, and improve collaboration across teams. This approach provides a more comprehensive and flexible solution for visualizing and analyzing MDC data, enhancing security posture management and decision-making.
Yuri Diogenes, Principal PM Manager, CxE Defender for Cloud
Tal Rosler, Senior PM lead, Microsoft Defender for Cloud
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.