Microsoft Power BI and Microsoft Defender for Cloud

Introduction

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.

 

Benefits of Using Power BI with Microsoft Defender for Cloud

1. Advanced Data Visualization: Power BI provides a wide array of visualization options, allowing security teams to create highly customized and visually rich dashboards that effectively communicate insights to different stakeholders.
2. Enhanced Data Analysis: Power BI's robust analytical tools, including DAX (Data Analysis Expressions) and built-in AI capabilities, enable security teams to perform complex data analysis and uncover deeper insights.
3. Seamless Integration: Power BI integrates with various data sources, including Azure Resource Graph, allowing you to consolidate data from multiple platforms into a single, unified view.
4. Collaborative Features: Power BI facilitates collaboration by enabling teams to share dashboards and reports easily, with role-based access controls ensuring data security.
5. Ease of Use: Power BI's intuitive drag-and-drop functionality makes it simple for users to create and customize visualizations without extensive technical knowledge, making it accessible to users of all skill levels.

 

Step-by-Step Guide to Integrating MDC Data into Power BI

To integrate MDC data into Power BI, follow these steps:

Step 1: Set Up Power BI and Azure Resource Graph

1. Install Power BI Desktop: Download Power BI Desktop.
2. Enable Azure Resource Graph: Ensure that you have the necessary permissions to access Azure Resource Graph.

Step 2: Connect Power BI to Azure Resource Graph

1. Open Power BI Desktop: Launch Power BI Desktop on your computer.
2. Get Data: Click on Get Data on the Home tab.
3. Select Azure Resource Graph: In the Get Data window, search for Azure Resource Graph and select it.
4. Connect: Click Connect and sign in with your Azure credentials.

 

 

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.

 

1. Enter ARG Queries: Write or paste the ARG KQL query and click OK
2. Load Data: After entering the queries, click Load to import the data into Power BI. The imported data will appear in the Fields pane, ready for you to create visualizations and reports.

 

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:

• Recommendations (by risk):

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

 

 

 

 

• Attack Paths:

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

 

 

 

 

• Secure Score:

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)

 

 

 

 

• Governance:

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

 

 

 

 

• Compliance:

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

1. Select Visualization Type: Choose from various visualizations such as charts, graphs, and maps to represent your data.
2. Customize Visualizations: Use the drag-and-drop functionality to customize your visualizations.
3. Create Dashboards: Arrange your visualizations into dashboards to provide a comprehensive view of your security data.

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

1. Publish Reports: Publish your reports to the Power BI service to share with your team.
2. Set Permissions: Use role-based access controls to manage who can view or edit the reports.

Conclusion

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.

 

 

Microsoft Defender for Cloud Additional Resources

• Watch a demonstration on how to use Governance Rule in this episode of Defender for Coud in the Field
• Download the new Microsoft CNAPP eBook at aka.ms/MSCNAPP
• Become a Defender for Cloud Ninja by taking the assessment at aka.ms/MDCNinja

Reviewers

Yuri Diogenes, Principal PM Manager, CxE Defender for Cloud

Tal Rosler, Senior PM lead, Microsoft Defender for Cloud