Automate Reporting of Defender for Cloud recommendations & Role Assignments with 35 different views

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 simple reporting-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:

          AngleComment
          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 securityFor 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 ITAs 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:

          Diagram that shows the core functionality of Microsoft Defender for Cloud.
          • Defender for Cloud secure score continually assesses your security posture so you can track new security opportunities and precisely report on the progress of your security efforts.
          • Defender for Cloud recommendations secures your workloads with step-by-step actions that protect your workloads from known security risks.
          • Defender for Cloud alerts defends 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)

          Link to query

          SecurityResources
          | where type == 'microsoft.security/assessments'
          | mvexpand Category=properties.metadata.categories
          | extend AssessmentId=id,
              AssessmentKey=name,
              ResourceId=properties.resourceDetails.Id,
              ResourceIdsplit = split(properties.resourceDetails.Id,'/'),
              RecommendationId=name,
              RecommendationName=properties.displayName,
              Source=properties.resourceDetails.Source,
              RecommendationState=properties.status.code,
              ActionDescription=properties.metadata.description,
              AssessmentType=properties.metadata.assessmentType,
              RemediationDescription=properties.metadata.remediationDescription,
              PolicyDefinitionId=properties.metadata.policyDefinitionId,
              ImplementationEffort=properties.metadata.implementationEffort,
              RecommendationSeverity=properties.metadata.severity,
              Threats=properties.metadata.threats,
              UserImpact=properties.metadata.userImpact,
              AzPortalLink=properties.links.azurePortal,
              MoreInfo=properties
          | extend ResourceSubId = tostring(ResourceIdsplit[(2)]),
              ResourceRgName = tostring(ResourceIdsplit[(4)]),
              ResourceType = tostring(ResourceIdsplit[(6)]),
              ResourceName = tostring(ResourceIdsplit[(8)]),
              FirstEvaluationDate = MoreInfo.status.firstEvaluationDate,
              StatusChangeDate = MoreInfo.status.statusChangeDate,
              Status = MoreInfo.status.code
          | join kind=leftouter (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
          | where AssessmentType == 'BuiltIn'
          | project-away kind,managedBy,sku,plan,tags,identity,zones,location,ResourceIdsplit,id,name,type,resourceGroup,subscriptionId, extendedLocation,subscriptionId1
          | project SubName, ResourceSubId, ResourceRgName,ResourceType,ResourceName,TenantId=tenantId, RecommendationName, RecommendationId, RecommendationState, RecommendationSeverity, AssessmentType, PolicyDefinitionId, ImplementationEffort, UserImpact, Category, Threats, Source, ActionDescription, RemediationDescription, MoreInfo, ResourceId, AzPortalLink, AssessmentKey
          | where RecommendationState == 'Unhealthy'
          | join kind=leftouter (
              securityresources
              | where type == 'microsoft.security/assessments/subassessments'
              | extend AssessmentKey = extract('.*assessments/(.+?)/.*',1,  id)
                  | project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
                  | extend SubAssessmentDescription = properties.description,
                      SubAssessmentDisplayName = properties.displayName,
                      SubAssessmentResourceId = properties.resourceDetails.id,
                      SubAssessmentResourceSource = properties.resourceDetails.source,
                      SubAssessmentCategory = properties.category,
                      SubAssessmentSeverity = properties.status.severity,
                      SubAssessmentCode = properties.status.code,
                      SubAssessmentTimeGenerated = properties.timeGenerated,
                      SubAssessmentRemediation = properties.remediation,
                      SubAssessmentImpact = properties.impact,
                      SubAssessmentVulnId = properties.id,
                      SubAssessmentMoreInfo = properties.additionalData,
                      SubAssessmentMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,
                      SubAssessmentMoreInfoData = properties.additionalData.data
          ) on AssessmentKey

          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)

          Link to query

          SecurityResources
          | where type == 'microsoft.security/assessments'
          | mvexpand Category=properties.metadata.categories
          | extend AssessmentId=id,
              AssessmentKey=name,
              ResourceId=properties.resourceDetails.Id,
              ResourceIdsplit = split(properties.resourceDetails.Id,'/'),
              RecommendationId=name,
              RecommendationName=properties.displayName,
              Source=properties.resourceDetails.Source,
              RecommendationState=properties.status.code,
              ActionDescription=properties.metadata.description,
              AssessmentType=properties.metadata.assessmentType,
              RemediationDescription=properties.metadata.remediationDescription,
              PolicyDefinitionId=properties.metadata.policyDefinitionId,
              ImplementationEffort=properties.metadata.implementationEffort,
              RecommendationSeverity=properties.metadata.severity,
              Threats=properties.metadata.threats,
              UserImpact=properties.metadata.userImpact,
              AzPortalLink=properties.links.azurePortal,
              MoreInfo=properties
          | extend ResourceSubId = tostring(ResourceIdsplit[(2)]),
              ResourceRgName = tostring(ResourceIdsplit[(4)]),
              ResourceType = tostring(ResourceIdsplit[(6)]),
              ResourceName = tostring(ResourceIdsplit[(8)]),
              FirstEvaluationDate = MoreInfo.status.firstEvaluationDate,
              StatusChangeDate = MoreInfo.status.statusChangeDate,
              Status = MoreInfo.status.code
          | join kind=leftouter (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
          | where AssessmentType == 'BuiltIn'
          | project-away kind,managedBy,sku,plan,tags,identity,zones,location,ResourceIdsplit,id,name,type,resourceGroup,subscriptionId, extendedLocation,subscriptionId1
          | project SubName, ResourceSubId, ResourceRgName,ResourceType,ResourceName,TenantId=tenantId, RecommendationName, RecommendationId, RecommendationState, RecommendationSeverity, AssessmentType, PolicyDefinitionId, ImplementationEffort, UserImpact, Category, Threats, Source, ActionDescription, RemediationDescription, MoreInfo, ResourceId, AzPortalLink, AssessmentKey
          | where RecommendationState == 'Unhealthy'

          Query #3 – Get Defender for Cloud SubAssessments

          Link to query

          SecurityResources
          | where type == 'microsoft.security/assessments/subassessments'
          | extend AssessmentKey = extract('.*assessments/(.+?)/.*',1,  id)
          | project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
          | extend SubAssessDescription = properties.description,
                   SubAssessDisplayName = properties.displayName,
                   SubAssessResourceId = properties.resourceDetails.id,
                   SubAssessResourceSource = properties.resourceDetails.source,
                   SubAssessCategory = properties.category,
                   SubAssessSeverity = properties.status.severity,
                   SubAssessCode = properties.status.code,
                   SubAssessTimeGenerated = properties.timeGenerated,
                   SubAssessRemediation = properties.remediation,
                   SubAssessImpact = properties.impact,
                   SubAssessVulnId = properties.id,
                   SubAssessMoreInfo = properties.additionalData,
                   SubAssessMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,
                   SubAssessMoreInfoData = properties.additionalData.data
          | join kind=leftouter (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId

          Using Powershell to run query

          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.

          Link to query

          $MDC_Recommendations_SubAssessments = @()
          $pageSize = 1000
          $iteration = 0
          $searchParams = @{
          					Query = "SecurityResources `
          							| where type == 'microsoft.security/assessments/subassessments'
          							| extend AssessmentKey = extract('.*assessments/(.+?)/.*',1,  id)
          							| project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
          							| extend SubAssessDescription = properties.description,
          									SubAssessDisplayName = properties.displayName,
          									SubAssessResourceId = properties.resourceDetails.id,
          									SubAssessResourceSource = properties.resourceDetails.source,
          									SubAssessCategory = properties.category,
          									SubAssessSeverity = properties.status.severity,
          									SubAssessCode = properties.status.code,
          									SubAssessTimeGenerated = properties.timeGenerated,
          									SubAssessRemediation = properties.remediation,
          									SubAssessImpact = properties.impact,
          									SubAssessVulnId = properties.id,
          									SubAssessMoreInfo = properties.additionalData,
          									SubAssessMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,
          									SubAssessMoreInfoData = properties.additionalData.data `
          							| join kind=leftouter (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId "
          						First = $pageSize
          					}
          
          $results = do {
          	$iteration += 1
          	Write-Verbose "Iteration #$iteration" -Verbose
          	$pageResults = Search-AzGraph  @searchParams -ManagementGroup $Global:ManagementGroupScope
          	$searchParams.Skip += $pageResults.Count
          	$MDC_Recommendations_SubAssessments += $pageResults
          } while ($pageResults.Count -eq $pageSize)

          Reporting-script | Views (samples)

          Below you can find sample views from the Excel spreadsheet. Data is coming from a small test environment with a few subscriptions and resources.

          Try it out in your own environment to see your own data 🙂

          PT_SUBASSESS_OTHER

          PT_SUBASSESS_IDENTITY

          PT_RBAC_ROLEDEF

          PT_RBAC_DIRECT_SUB

          PT_CATEGORY_DATA

          PT_CATEGORY_NETWORKING

          PT_RBAC_SCOPE_DELEGATION

          PT_CATEGORY_SUBLEVEL

          Prioritize recommendations based on Category and RecommendationSeverity

          PT_CATEGORY_COMPUTE

          PT_RESOURCETYPE

          PT_CATEGORY_RESOURCELEVEL

          PT_RBAC_MG

          Structure | Excel | Tables

          TableNamePurposeSource
          Unhealthy_AllShow All Unhealthy recommendationsDefender for Cloud
          Extracted via Azure Resource Graph
          Unhealthy_HighShow all Unhealthy recommendations with High priorityFiltered view of Unhealthy_All
          Unhealthy_MediumShow all Unhealthy recommendations with medium priorityFiltered view of Unhealthy_All
          Unhealthy_LowShow all Unhealthy recommendations with Low priorityFiltered view of Unhealthy_All
          Unhealthy_All_SubLevelShow all Unhealthy recommendations where the target is on subcription-levelFiltered view of Unhealthy_All
          Unhealthy_All_ResourceLevelShow all Unhealthy recommendations where the target is on resource-levelFiltered view of Unhealthy_All
          Unhealthy_Category_NetworkingShow all Unhealthy recommendations related to Networking categoryFiltered view of Unhealthy_All
          Unhealthy_Category_AppServicesShow all Unhealthy recommendations related to AppServices categoryFiltered view of Unhealthy_All
          Unhealthy_Category_ComputeShow all Unhealthy recommendations related to Compute categoryFiltered view of Unhealthy_All
          Unhealthy_Category_ContainerShow all Unhealthy recommendations related to Container categoryFiltered view of Unhealthy_All
          Unhealthy_Category_DataShow all Unhealthy recommendations related to Data categoryFiltered view of Unhealthy_All
          Unhealthy_Category_IoTShow all Unhealthy recommendations related to IoT categoryFiltered view of Unhealthy_All
          Unhealthy_Category_Id_AccessShow all Unhealthy recommendations related to IdentityAndAccess categoryFiltered view of Unhealthy_All
          SubAssess_AllShow all detailed information (SubAssessments)Defender for Cloud
          Extracted via Azure Resource Graph
          SubAssess_IdentityShow all identity detailed information (SubAssessments)Filtered view of SubAssess_All
          SubAssess_OtherShow all other, non-identity detailed information (SubAssessments)Filtered view of SubAssess_All
          RBAC_RoleAssignmentsShow all Azure RBAC Role AssignmentsAzure Role Assignsment
          RBAC_Direct_SublevelShow all Azure RBAC Role Assignments directly on Sub-level (not part of group)Filtered of RBAC_RoleAssignments
          RBAC_Direct_MglevelShow all Azure RBAC Role Assignments directly on Mg-level (not part of group)Filtered of RBAC_RoleAssignments

          Structure | Excel | Pivot tables

          Pivot table namePurposeSource TableSort Order
          PT_CATEGORY_SUBLEVELPrioritize recommendations based on Category and RecommendationSeverityUnhealthy_All_SubLevel(1) Category (Storage, Network, Identity, etc.)
          (2) RecommendationSeverity (High, Medium, Low)
          (3) RecommendationName
          (4) SubName (suscription name)
          PT_CATEGORY_RESOURCELEVELPrioritize recommendations based on ResourceType and RecommendationSeverityUnhealthy_All_ResourceLevel(1) Category (Storage, Network, Identity, etc.)
          (2) RecommendationSeverity (High, Medium, Low)
          (3) RecommendationName
          (4) SubName (suscription name)
          (5) ResourceRgName
          (6) ResourceName
          PT_CATEGORY_NETWORKINGPrioritize networking recommendations based on RecommendationSeverityUnhealthy_Category_Networking(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_CATEGORY_APPSERVICESPrioritize AppServices recommendations based on RecommendationSeverityUnhealthy_Category_AppServices(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_CATEGORY_COMPUTEPrioritize Compute recommendations based on RecommendationSeverityUnhealthy_Category_Compute(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_CATEGORY_CONTAINERPrioritize Container recommendations based on RecommendationSeverityUnhealthy_Category_Container(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_CATEGORY_DATAPrioritize Data recommendations based on RecommendationSeverityUnhealthy_Category_Data(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_CATEGORY_ID_ACCESSPrioritize Identity and Access recommendations based on RecommendationSeverityUnhealthy_Category_IdentityAndAccess(1) RecommendationSeverity (High, Medium, Low)
          (2) RecommendationName
          (3) SubName (suscription name)
          (4) ResourceRgName
          (5) ResourceName
          PT_RESOURCETYPEPrioritize recommendations based on ResourceType and RecommendationSeverityUnhealthy_All_ResourceLevel(1) ResourceType (SQL, Keyvault, Storage, Network, Identity, etc.)
          (2) RecommendationSeverity (High, Medium, Low)
          (3) RecommendationName
          (4) SubName (suscription name)
          (5) ResourceRgName
          (6) ResourceName
          PT_CATEGORY_RESOURCELEVELPrioritize recommendations based on Category and RecommendationSeverityUnhealthy_All_ResourceLevel(1) Category (Storage, Network, Identity, etc.)
          (2) RecommendationSeverity (High, Medium, Low)
          (3) RecommendationName
          (4) SubName (suscription name)
          (5) ResourceRgName
          (6) ResourceName
          PT_SUBASSESS_IDENTITYPrioritize recommendations based on SubAssessmentSeverity (identity-related)SubAssess_Identity(1) SubAssessSeverity (High, Medium, Low)
          (2) SubAssessDisplayName (recommendation)
          (3) SubAssessResDisplayName (resource)
          (4) SubAssessResUserPrincipalName (resource)
          (5) SubName (suscription name)
          PT_SUBASSESS_OTHERPrioritize recommendations based on SubAssessmentSeverity (non-identity related)SubAssess_Other(1) SubAssessSeverity (High, Medium, Low)
          (2) SubAssessDisplayName (recommendation)
          (3) SubAssessResDisplayName (resource)
          (4) SubAssessResUserPrincipalName (resource)
          (5) SubName (suscription name)
          PT_RBAC_ROLEDEFDetail RBAC permissions, sorted by RoleDefinitionName and Scope_DelegationRBAC_RoleAssignments(1) SubscriptionName
          (2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)
          (3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)
          (4) Scope (management group name or /)
          (5) RBAC_Delegation_Type (Group_inheritance, Direct)
          (6) RBAC_GroupName
          (7) ObjectType
          (8) DisplayName
          (9) UserPrincipalName
          PT_RBAC_SCOPE_DELEGATIONDetail RBAC permissions, sorted by Scope_Delegation and RoleDefinitionNameRBAC_RoleAssignments(1) SubscriptionName
          (2) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)
          (3) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)
          (4) Scope (management group name or /)
          (5) RBAC_Delegation_Type (Group_inheritance, Direct)
          (6) RBAC_GroupName
          (7) ObjectType
          (8) DisplayName
          (9) UserPrincipalName
          PT_RBAC_MGDetect direct RBAC permissions on Mg-level (not done by group)RBAC_Direct_Mglevel(1) SubscriptionName
          (2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)
          (3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)
          (4) Scope (management group name or /)
          (5) ObjectType
          (6) DisplayName
          (7) UserPrincipalName
          PT_RBAC_SUBDetect direct RBAC permissions on Sub-level (not done by group)RBAC_Direct_Sublevel(1) SubscriptionName
          (2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)
          (3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)
          (4) Scope (management group name or /)
          (5) ObjectType
          (6) DisplayName
          (7) UserPrincipalName

          Script flow overview

          The script flow is:

          (1) Connect to Azure
          (2) Collect Azure Role Assignments
          (3) Collect Microsoft Defender for Cloud recommendations via Azure Resource Graph

          * MDC | Recommendations with link
          * MDC | SubAssessments (Detailed Infomation)
          * SubAssessment Identity Lookup
          (4) Prepare different filtering-variables
          (5) Delete existing report file, if found
          (6) Build Introduction table – by reading content from content.csv file (can be adjusted). Includes special formatting
          (7) Export tables to Excel
          (8) Build pivot tables array – including spec
          (9) Build pivots from array

          Implementation of script

          AccountYou need to create a service principal – or use your own account for testing

          Account must be having READ permissions on the level, where you want to report the status from (typically tenant or management group-level)
          PowershellScript uses the following 4 Powershell modules:

          AzureAD (used to lookup Azure AD object information)

          Az.* – used to connect to Azure

          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.
          VariablesYou 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

          Sort-order in Pivot

          Feel free to adjust the sort-order to your needs.

          1 thought on “Automate Reporting of Defender for Cloud recommendations & Role Assignments with 35 different views”

          Leave a Reply