In this blog, I will demonstrate, how you can extract security recommendations from Microsoft Defender for Cloud using Azure Resource Graph – delivering a horizontal cross-subscriptions, workload overview. Data will automatically be exported into a Excel spreadsheet with 19 Excel tables and 16 pivot tables.
Information can be used to detect deviations from best practice / desired state to
Get-in-control with workloads in tenant/management group (storage, network, app services, containers, etc.), where we are not in control according to security best practice / desired state
Get-in-control with subscriptions, where Azure environments are not configured as recommended by Microsoft
Get-in-control with role assignments in tenant / management group / subscription / resource group.
Get detailed information about role assignments on user / service-principal-level, based on direct assignment and inheritance
Get detailed insight about users / service-principal-level, based on group membership – both direct and inheritance.
Feel free to download the script in my github – and try it out in your own environment. Microsoft Defender for Cloud is a pre-requisite.
Collection of Role Assignments, both direct and via group-membership
Background
Recently, I was asked to build a simplereporting-script, which integrates data from Microsoft Defender for Cloud and Azure Role Assignments. Data should cover whole tenant / management groups / all subscriptions.
Solution should present a global view with different dimensions aimed for different target audience (cloud architects, workload specialists, application owners, operation specialists, management).
Solution should output data into Excel tables & pivot tables. Excel was chosen as it delivers an offline report, which can easily be used for simulations, easy to change reporting order/filter, easy to distribute Excel file, integration to task management & prioritization, easy to do cost forecast for changes, etc.
Data should be integrated into different platforms, if needed (LogAnalytics workbooks/dashboards, PowerBI, Azure Monitor, CMDB, etc).
We should also use script for alerting purpose.
Report-build should be 100% automated – delivering a report with defined frequency.
Script download
Feel free to download the script in my github – and try it out in your own environment. Microsoft Defender for Cloud is a prerequisite.
If you scroll further down in this blog, you will find the following information:
Script objective
Introduction to Microsoft Defender for Cloud
Azure Resource Graph queries for Microsoft Defender for Cloud Recommendations
Reporting-script | Views (samples)
Structure | Excel | Tables
Structure | Excel | Pivot tables
Script flow overview
Implementation of script
Tips & Tricks
Get started introduction (overview)
Navigation-view in Excel
Filtering
Sort-order in Pivot
Script objective
The script can be used as an accelerator to build a global overview – and also to monitor the journey getting automation, operating model, governance up-and-running:
Angle
Comment
Get-in-control with existing Azure infrastructure
Detect environments & workloads in tenant / management group, where we are not in control
Help to detect deviations from best practice / desired state
Get-in-control with subscriptions, where environment are not configured according to security best practice / desired state
Get-in-control with workloads in tenant/management group (storage, network, app services, containers, etc.) where we are not in control according to security best practice / desired state
Get-in-control with role assignments in tenant / management group / subscription / resource group.
Get detailed information about role assignments on user / service-principal-level, based on direct assignment and inheritance
Get detailed insight about users / service-principal-level, based on group membership – both direct and inheritance.
Increase maturity around operating model & governance – ‘organizational readiness’
Implementation of ‘policy triggers’
Establish of Governance Board
I have been numerous examples of Azure environments, where increasing demands from the business combined with a lack of maturity in IT have resulted in critical gaps in security caused by multiple teams responsible for the environment – a result of missing governance and operating model – in short: ‘responsibility’ – who, what, when, how ?
Insight from reports can be used to show different teams ‘as-is’ – and be used as part of establishing needed governance and operating model.
In enterprise environments, I do often see deviations in development-environments, where configurations are done using the portal. Of course, Azure policies and automation are key to enforce a desired state (“to-be”), but it also requires maturity and governance.
Often I see enterprises having different tools like Github, GitLab, DevOps, Terraform, Bicep. It is important, that the policies, naming conventions, security lockdown, tags, etc. are managed using a single set of policies.
It is important to ensure skills and competencies to the involved parties.
Large-scale operation & Automation
Governance-board & Change management
Packaged / templates / Deployment Repositories
In order to lower operation-costs, increase standardization – while keeping the environment in separate landing zones and managed by workload-specialist teams, you will often implement large-scale operation processes. These processes handles backup, monitoring, patching, lifecycle processes, performance monitoring, log management, security configuration, etc.
Insight from this script can be used to include the recommendations into the provisioning solution / Infrastructure-As-Code (Github, Azure DevOps, Terraform, Biceps, etc.).
Often I combine a provisioning solution, with a set of automation-scripts, which enforces desired-state with dynamic update from CMDB, automatic self-mitigation of issues, reporting & dashboards, alerting, etc.
Migrate to new features to increase security
For example, you can use private endpoints & private links moving the traffic to internal VNET – instead of communicating using public endpoints.
The recommendation from Defender includes these new features.
Structure of IT
As organizations are structured differently (and changes happens), I have also seen gaps in environments, especially in de-centralized environments having local responsibility.
Information from the reports can detect deviations.
Introduction to Microsoft Defender for Cloud
Microsoft Defender for Cloud is a Cloud Security Posture Management (CSPM) and Cloud Workload Protection Platform (CWPP) for all of your Azure, on-premises, and multicloud (Amazon AWS and Google GCP) resources. Defender for Cloud fills three vital needs as you manage the security of your resources and workloads in the cloud and on-premises:
Defender for Cloud secure scorecontinually assesses your security posture so you can track new security opportunities and precisely report on the progress of your security efforts.
Defender for Cloud recommendationssecures your workloads with step-by-step actions that protect your workloads from known security risks.
Defender for Cloud alertsdefends your workloads in real-time so you can react immediately and prevent security events from developing.
Azure Resource Graph queries for Microsoft Defender for Cloud Recommendations
A fast way to get access to Microsoft Defender for Cloud recommendation is through Azure Resource Graph (ARG). Azure Resource Graph serve as an index of the configuration and will be updated very fast when changes happens.
Azure ARG use the RBAC permissions delegated to the account running the query.
Below I have added 3 samples of queries, that can be used to get the recommendations – using Azure Resource Graph.
Query #1 – Get Defender for Cloud recommendations – including SubAssessments (for example vulnerability recommendations)
This query will return all individual unhealthy recommendations in both assessment and subassessments. Dataset will be very large, so be patient 🙂 Consider to use query 2 and 3 instead, in case data-set is too big (or query is slow).
Query #2 – Get Defender for Cloud Recommendations – with links for more information (SubAssessments)
It is possible to run Azure Resource Graph query using Powershell using Search-AzGraph.
The example below covers pagination, as the result from ARG only covers a page size of 1000. In case of larger data, it must be retrieved using pagination.
Az.ResourceGraph – used to connect to Azure Resource Graph and run queries
ImportExcel – used to export data in Excel file Big thanx to Microsoft MVP Doug Finke for creating a fantastic powershell module for Excel.
Variables
You can customize the script header for your needs – including scoping via management groups – and exclude subscriptions, if needed.
See below
# Scope (MG)
# You can define the scope for the targeting, supporting management groups or tenant root id (all subs)
$Global:ManagementGroupScope = "xxxxxx" # can mg e.g. mg-company or AAD Id (=Tenant Root Id)
# Exclude list
# You can exclude certain subs, resource groups, resources, if you don't want to have them as part of the scope
$global:Exclude_Subscriptions = @("xxxxxxxxxxxxxxxxxxxxxx") # for example platform-connectivity
$global:Exclude_ResourceGroups = @()
$global:Exclude_Resource = @()
$global:Exclude_Resource_Contains = @()
$global:Exclude_Resource_Startswith = @()
$global:Exclude_Resource_Endswith = @()
# Content file - can be found on Github
$HelpContentFile = "C:\SCRIPTS\Azure-Recommendations-Get-In-Control\Content.csv"
# OutputFile
$FileOutput = "C:\SCRIPTS\Azure-Recommendations-Get-In-Control\Azure_Recommendations_Get-in-Control.xlsx"
Tips & Tricks
Get started introduction (overview)
As part of the build process, all tables, pivots tables, information about sort order, etc. will be added in the first table ‘Introduction_Help’
Content is read from the content.csv file.
Feel free to add more columns like Recommended Actions, Frequency, Responsible Team/person to fit your needs.
Navigation-view in Excel
In order to see all tabs, you can activate Navigation view in Excel
Filtering
Filtering capability is automatically activated as part of deployment
Excellent work! well written thank you Sir.