{"id":882,"date":"2023-02-01T23:45:00","date_gmt":"2023-02-01T22:45:00","guid":{"rendered":"https:\/\/mortenknudsen.net\/?p=882"},"modified":"2023-04-23T09:04:54","modified_gmt":"2023-04-23T08:04:54","slug":"automate-reporting-of-defender-for-cloud-recommendations-role-assignments-with-35-different-views","status":"publish","type":"post","link":"https:\/\/mortenknudsen.net\/?p=882","title":{"rendered":"Automate Reporting of Defender for Cloud recommendations &#038; Role Assignments with 35 different views"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">In this blog, I will demonstrate, how you can extract <strong>security recommendations<\/strong> from <strong>Microsoft Defender for Cloud<\/strong> using <strong>Azure Resource Graph<\/strong> &#8211; delivering a <strong>horizontal cross-subscriptions, workload overview<\/strong>. Data will automatically be exported into a <strong>Excel spreadsheet<\/strong> with <strong>19 Excel tables<\/strong> and <strong>16 pivot tables<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Information can be used to detect <strong>deviations<\/strong> from <strong>best practice \/ desired state<\/strong> to <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Get-in-control with <strong>workloads in tenant\/management group<\/strong> (storage, network, app services, containers, etc.), where we are not in control according to <strong>security<\/strong> best practice \/ desired state<\/li>\n\n\n\n<li>Get-in-control with <strong>subscriptions<\/strong>, where Azure environments are not configured as recommended by Microsoft<\/li>\n\n\n\n<li>Get-in-control with <strong>role assignments in tenant \/ management group \/ subscription \/ resource group<\/strong>.<\/li>\n\n\n\n<li>Get detailed information about <strong>role assignments<\/strong> on <strong>user<\/strong> \/ <strong>service-principal-level<\/strong>, based on <strong>direct assignment<\/strong> and <strong>inheritance<\/strong><\/li>\n\n\n\n<li>Get detailed insight about <strong>users \/ service-principal-level<\/strong>, based on <strong>group membership<\/strong> &#8211; both <strong>direct<\/strong> and <strong>inheritance<\/strong>.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\"><\/ul>\n\n\n\n<ul class=\"wp-block-list\"><\/ul>\n\n\n\n<ul class=\"wp-block-list\"><\/ul>\n<cite><br>Feel free to download the script in <a href=\"https:\/\/github.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\" target=\"_blank\" rel=\"noreferrer noopener\">my github<\/a> &#8211; and try it out in your own environment. Microsoft Defender for Cloud is a pre-requisite.<\/cite><\/blockquote>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1396\" height=\"1046\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1.jpg\" alt=\"\" class=\"wp-image-922\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1.jpg 1396w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1-300x225.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1-1024x767.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1-768x575.jpg 768w\" sizes=\"auto, (max-width: 1396px) 100vw, 1396px\" \/><figcaption class=\"wp-element-caption\">Collection of Role Assignments, both direct and via group-membership<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1347\" height=\"489\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/ExportExcel.jpg\" alt=\"\" class=\"wp-image-924\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/ExportExcel.jpg 1347w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/ExportExcel-300x109.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/ExportExcel-1024x372.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/ExportExcel-768x279.jpg 768w\" sizes=\"auto, (max-width: 1347px) 100vw, 1347px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"505\" height=\"1076\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1.jpg\" alt=\"\" class=\"wp-image-892\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1.jpg 505w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1-141x300.jpg 141w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1-481x1024.jpg 481w\" sizes=\"auto, (max-width: 505px) 100vw, 505px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"702\" height=\"314\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING.jpg\" alt=\"\" class=\"wp-image-899\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING.jpg 702w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING-300x134.jpg 300w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Background<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-34e1ba17-373f-4523-8c18-ea7f011be544\">Recently, I was asked to build a <strong>simple<\/strong> <strong>reporting<\/strong>-script<strong>,<\/strong> which integrates data from <strong>Microsoft Defender for Cloud<\/strong> and <strong>Azure Role Assignments<\/strong>. Data should cover<strong> whole tenant \/ management groups \/ all subscriptions<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-6d1cfec5-42b8-4aca-9199-5faceecbe512\">Solution should present a <strong>global view<\/strong> with <strong>different dimensions<\/strong> aimed for <strong>different target audience<\/strong> (cloud architects, workload specialists, application owners, operation specialists, management).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-992d2356-1dd1-4119-84bf-87fd6a51f47b\">Solution should output data into <strong>Excel tables &amp; pivot tables<\/strong>. Excel was chosen as it delivers an <strong>offline report<\/strong>, which can easily be used for <strong>simulations<\/strong>, <strong>easy to change reporting order\/filter<\/strong>, <strong>easy to distribute Excel file<\/strong>, <strong>integration to task management<\/strong> &amp; <strong>prioritization<\/strong>, easy to do <strong>cost forecast for changes<\/strong>, etc. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-992d2356-1dd1-4119-84bf-87fd6a51f47b\">Data should <strong>be integrated into different platforms<\/strong>, if needed (LogAnalytics workbooks\/dashboards, PowerBI, Azure Monitor, CMDB, etc). <\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-992d2356-1dd1-4119-84bf-87fd6a51f47b\">We should also use script for <strong>alerting purpose<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-be24cb78-241d-4309-814d-8e149160c769\">Report-build should be <strong>100% automated<\/strong> &#8211; delivering a <strong>report with defined frequency<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Script download<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"block-be24cb78-241d-4309-814d-8e149160c769\">Feel free to download the script in <a href=\"https:\/\/github.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\" target=\"_blank\" rel=\"noreferrer noopener\">my github<\/a> &#8211; and try it out in your own environment. Microsoft Defender for Cloud is a prerequisite.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you scroll further down in this blog, you will find the following information:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Script objective<\/li>\n\n\n\n<li>Introduction to Microsoft Defender for Cloud<\/li>\n\n\n\n<li>Azure Resource Graph queries for Microsoft Defender for Cloud Recommendations<\/li>\n\n\n\n<li>Reporting-script | Views (samples)<\/li>\n\n\n\n<li>Structure | Excel | Tables<\/li>\n\n\n\n<li>Structure | Excel | Pivot tables<\/li>\n\n\n\n<li>Script flow overview<\/li>\n\n\n\n<li>Implementation of script<\/li>\n\n\n\n<li>Tips &amp; Tricks\n<ul class=\"wp-block-list\">\n<li>Get started introduction (overview)<\/li>\n\n\n\n<li>Navigation-view in Excel<\/li>\n\n\n\n<li>Filtering<\/li>\n\n\n\n<li>Sort-order in Pivot<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Script objective<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The script can be used as an <strong>accelerator<\/strong> to <strong>build a global overview<\/strong> &#8211; and also <strong>to monitor the journey getting automation, operating model, governance up-and-running<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><tbody><tr><td>Angle<\/td><td>Comment<\/td><\/tr><tr><td><strong>Get-in-control with existing Azure infrastructure<\/strong><br><br><strong>Detect environments &amp; workloads in tenant \/ management group, where we are not in control<\/strong><\/td><td>Help to detect <strong>deviations<\/strong> from <strong>best practice  \/ desired state<\/strong><br><br>Get-in-control with <strong>subscriptions<\/strong>, where environment are not configured according to <strong>security<\/strong> best practice \/ desired state<br><br>Get-in-control with <strong>workloads in tenant\/management group<\/strong> (storage, network, app services, containers, etc.) where we are not in control according to <strong>security<\/strong> best practice \/ desired state<br><br>Get-in-control with <strong>role assignments in tenant \/ management group \/ subscription \/ resource group<\/strong>.<br><br>Get detailed information about <strong>role assignments<\/strong> on <strong>user<\/strong> \/ <strong>service-principal-level<\/strong>, based on <strong>direct assignment<\/strong> and <strong>inheritance<\/strong><br><br>Get detailed insight about <strong>users \/ service-principal-level<\/strong>, based on <strong>group membership<\/strong> &#8211; both <strong>direct<\/strong> and <strong>inheritance<\/strong>.<\/td><\/tr><tr><td><strong>Increase maturity around operating model &amp; governance &#8211; &#8216;organizational readiness&#8217;<br><br>Implementation of &#8216;policy triggers&#8217;<\/strong><br><br><strong>Establish of Governance Board<\/strong><\/td><td>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 &#8211; a result of missing governance and operating model &#8211; in short: <strong>&#8216;responsibility&#8217; &#8211; who, what, when, how ?<\/strong> <br><br>Insight from reports can be used <strong>to show different teams &#8216;as-is&#8217; <\/strong>&#8211; and be used as part of <strong>establishing needed governance and operating model. <\/strong><br><br>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 (&#8220;to-be&#8221;), but it also requires maturity and governance.<br><br>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.<br><br>It is important to ensure skills and competencies to the involved parties.<\/td><\/tr><tr><td><strong>Large-scale operation &amp; Automation <br><br>Governance-board &amp; Change management<br><br>Packaged \/ templates \/ Deployment Repositories<\/strong><\/td><td>In order to lower operation-costs, increase standardization &#8211; 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.<br><br>Insight from this script can be used to include the recommendations into the provisioning solution \/ Infrastructure-As-Code (Github, Azure DevOps, Terraform, Biceps, etc.).<br><br>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 &amp; dashboards, alerting, etc.<\/td><\/tr><tr><td><strong>Migrate to new features to increase security<\/strong><\/td><td>For example, you can use private endpoints &amp; private links moving the traffic to internal VNET &#8211; instead of communicating using public endpoints.<br><br>The recommendation from Defender includes these new features.<\/td><\/tr><tr><td><strong>Structure of IT<\/strong><\/td><td>As organizations are structured differently (and changes happens), I have also seen gaps in environments, especially in de-centralized environments having local responsibility.<br><br>Information from the reports can detect deviations.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to Microsoft Defender for Cloud<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/learn.microsoft.com\/en-us\/azure\/defender-for-cloud\/media\/defender-for-cloud-introduction\/defender-for-cloud-synopsis.png\" alt=\"Diagram that shows the core functionality of Microsoft Defender for Cloud.\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/defender-for-cloud\/secure-score-security-controls\"><strong>Defender for Cloud secure score<\/strong><\/a>&nbsp;<strong>continually assesses<\/strong>&nbsp;your security posture so you can track new security opportunities and precisely report on the progress of your security efforts.<\/li>\n\n\n\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/defender-for-cloud\/security-policy-concept\"><strong>Defender for Cloud recommendations<\/strong><\/a>&nbsp;<strong>secures<\/strong>&nbsp;your workloads with step-by-step actions that protect your workloads from known security risks.<\/li>\n\n\n\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/defender-for-cloud\/alerts-overview\"><strong>Defender for Cloud alerts<\/strong><\/a>&nbsp;<strong>defends<\/strong>&nbsp;your workloads in real-time so you can react immediately and prevent security events from developing.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Azure Resource Graph queries for Microsoft Defender for Cloud Recommendations<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">A fast way to get access to <strong>Microsoft Defender for Cloud recommendation<\/strong> is through <strong>Azure Resource Graph (ARG)<\/strong>. Azure Resource Graph serve as an index of the configuration and will be updated very fast when changes happens.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Azure ARG use the RBAC permissions delegated to the account running the query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Below I have added 3 samples of queries, that can be used to get the recommendations &#8211; using Azure Resource Graph.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-accent-2-color has-pale-ocean-gradient-background has-text-color has-background\">Query #1 &#8211; Get Defender for Cloud recommendations &#8211; including SubAssessments (for example vulnerability recommendations)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\/main\/Query%201%20%E2%80%93%20Get%20Defender%20for%20Cloud%20recommendations%20%E2%80%93%20including%20SubAssessments%20(for%20example%20vulnerability%20recommendations).txt\" target=\"_blank\" rel=\"noreferrer noopener\">Link to query<\/a><\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<pre class=\"wp-block-code has-small-font-size\"><code>SecurityResources\n| where type == 'microsoft.security\/assessments'\n| mvexpand Category=properties.metadata.categories\n| extend AssessmentId=id,\n    AssessmentKey=name,\n    ResourceId=properties.resourceDetails.Id,\n    ResourceIdsplit = split(properties.resourceDetails.Id,'\/'),\n    RecommendationId=name,\n    RecommendationName=properties.displayName,\n    Source=properties.resourceDetails.Source,\n    RecommendationState=properties.status.code,\n    ActionDescription=properties.metadata.description,\n    AssessmentType=properties.metadata.assessmentType,\n    RemediationDescription=properties.metadata.remediationDescription,\n    PolicyDefinitionId=properties.metadata.policyDefinitionId,\n    ImplementationEffort=properties.metadata.implementationEffort,\n    RecommendationSeverity=properties.metadata.severity,\n    Threats=properties.metadata.threats,\n    UserImpact=properties.metadata.userImpact,\n    AzPortalLink=properties.links.azurePortal,\n    MoreInfo=properties\n| extend ResourceSubId = tostring(ResourceIdsplit&#91;(2)]),\n    ResourceRgName = tostring(ResourceIdsplit&#91;(4)]),\n    ResourceType = tostring(ResourceIdsplit&#91;(6)]),\n    ResourceName = tostring(ResourceIdsplit&#91;(8)]),\n    FirstEvaluationDate = MoreInfo.status.firstEvaluationDate,\n    StatusChangeDate = MoreInfo.status.statusChangeDate,\n    Status = MoreInfo.status.code\n| join kind=leftouter (resourcecontainers | where type=='microsoft.resources\/subscriptions' | project SubName=name, subscriptionId) on subscriptionId\n| where AssessmentType == 'BuiltIn'\n| project-away kind,managedBy,sku,plan,tags,identity,zones,location,ResourceIdsplit,id,name,type,resourceGroup,subscriptionId, extendedLocation,subscriptionId1\n| 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\n| where RecommendationState == 'Unhealthy'\n| join kind=leftouter (\n    securityresources\n    | where type == 'microsoft.security\/assessments\/subassessments'\n    | extend AssessmentKey = extract('.*assessments\/(.+?)\/.*',1,  id)\n        | project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId\n        | extend SubAssessmentDescription = properties.description,\n            SubAssessmentDisplayName = properties.displayName,\n            SubAssessmentResourceId = properties.resourceDetails.id,\n            SubAssessmentResourceSource = properties.resourceDetails.source,\n            SubAssessmentCategory = properties.category,\n            SubAssessmentSeverity = properties.status.severity,\n            SubAssessmentCode = properties.status.code,\n            SubAssessmentTimeGenerated = properties.timeGenerated,\n            SubAssessmentRemediation = properties.remediation,\n            SubAssessmentImpact = properties.impact,\n            SubAssessmentVulnId = properties.id,\n            SubAssessmentMoreInfo = properties.additionalData,\n            SubAssessmentMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,\n            SubAssessmentMoreInfoData = properties.additionalData.data\n) on AssessmentKey<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-luminous-vivid-amber-to-luminous-vivid-orange-gradient-background has-background wp-block-paragraph\">This query will return all individual unhealthy recommendations in both assessment and subassessments. Dataset will be <strong>very large<\/strong>, so be patient \ud83d\ude42 Consider to use query 2 and 3 instead, in case data-set is too big (or query is slow).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1596\" height=\"1084\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3.jpg\" alt=\"\" class=\"wp-image-917\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3.jpg 1596w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3-300x204.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3-1024x695.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3-768x522.jpg 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query1-3-1536x1043.jpg 1536w\" sizes=\"auto, (max-width: 1596px) 100vw, 1596px\" \/><\/figure>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading has-pale-ocean-gradient-background has-background\">Query #2 &#8211; Get Defender for Cloud Recommendations &#8211; with links for more information (SubAssessments)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\/main\/Query%202%20%E2%80%93%20Get%20Defender%20for%20Cloud%20Recommendations%20%E2%80%93%20with%20links%20for%20more%20information%20(SubAssessments).txt\" target=\"_blank\" rel=\"noreferrer noopener\">Link to query<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>SecurityResources\n| where type == 'microsoft.security\/assessments'\n| mvexpand Category=properties.metadata.categories\n| extend AssessmentId=id,\n    AssessmentKey=name,\n    ResourceId=properties.resourceDetails.Id,\n    ResourceIdsplit = split(properties.resourceDetails.Id,'\/'),\n    RecommendationId=name,\n    RecommendationName=properties.displayName,\n    Source=properties.resourceDetails.Source,\n    RecommendationState=properties.status.code,\n    ActionDescription=properties.metadata.description,\n    AssessmentType=properties.metadata.assessmentType,\n    RemediationDescription=properties.metadata.remediationDescription,\n    PolicyDefinitionId=properties.metadata.policyDefinitionId,\n    ImplementationEffort=properties.metadata.implementationEffort,\n    RecommendationSeverity=properties.metadata.severity,\n    Threats=properties.metadata.threats,\n    UserImpact=properties.metadata.userImpact,\n    AzPortalLink=properties.links.azurePortal,\n    MoreInfo=properties\n| extend ResourceSubId = tostring(ResourceIdsplit&#91;(2)]),\n    ResourceRgName = tostring(ResourceIdsplit&#91;(4)]),\n    ResourceType = tostring(ResourceIdsplit&#91;(6)]),\n    ResourceName = tostring(ResourceIdsplit&#91;(8)]),\n    FirstEvaluationDate = MoreInfo.status.firstEvaluationDate,\n    StatusChangeDate = MoreInfo.status.statusChangeDate,\n    Status = MoreInfo.status.code\n| join kind=leftouter (resourcecontainers | where type=='microsoft.resources\/subscriptions' | project SubName=name, subscriptionId) on subscriptionId\n| where AssessmentType == 'BuiltIn'\n| project-away kind,managedBy,sku,plan,tags,identity,zones,location,ResourceIdsplit,id,name,type,resourceGroup,subscriptionId, extendedLocation,subscriptionId1\n| 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\n| where RecommendationState == 'Unhealthy'<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"2560\" height=\"1066\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-scaled.jpg\" alt=\"\" class=\"wp-image-909\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-scaled.jpg 2560w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-300x125.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-1024x426.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-768x320.jpg 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-1536x640.jpg 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query2-2048x853.jpg 2048w\" sizes=\"auto, (max-width: 2560px) 100vw, 2560px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading has-pale-ocean-gradient-background has-background\">Query #3 &#8211; Get Defender for Cloud SubAssessments<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\/main\/Query%203%20%E2%80%93%20Get%20Defender%20for%20Cloud%20SubAssessments.txt\" target=\"_blank\" rel=\"noreferrer noopener\">Link to query<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>SecurityResources\n| where type == 'microsoft.security\/assessments\/subassessments'\n| extend AssessmentKey = extract('.*assessments\/(.+?)\/.*',1,  id)\n| project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId\n| extend SubAssessDescription = properties.description,\n         SubAssessDisplayName = properties.displayName,\n         SubAssessResourceId = properties.resourceDetails.id,\n         SubAssessResourceSource = properties.resourceDetails.source,\n         SubAssessCategory = properties.category,\n         SubAssessSeverity = properties.status.severity,\n         SubAssessCode = properties.status.code,\n         SubAssessTimeGenerated = properties.timeGenerated,\n         SubAssessRemediation = properties.remediation,\n         SubAssessImpact = properties.impact,\n         SubAssessVulnId = properties.id,\n         SubAssessMoreInfo = properties.additionalData,\n         SubAssessMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,\n         SubAssessMoreInfoData = properties.additionalData.data\n| join kind=leftouter (resourcecontainers | where type=='microsoft.resources\/subscriptions' | project SubName=name, subscriptionId) on subscriptionId<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"2346\" height=\"1068\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3.jpg\" alt=\"\" class=\"wp-image-915\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3.jpg 2346w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3-300x137.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3-1024x466.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3-768x350.jpg 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3-1536x699.jpg 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Query3-2048x932.jpg 2048w\" sizes=\"auto, (max-width: 2346px) 100vw, 2346px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using Powershell to run query<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">It is possible to run Azure Resource Graph query using Powershell using <strong>Search-AzGraph<\/strong>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/Azure-Recommendations-Get-In-Control\/main\/Powershell-Query_Search-AzGraph.txt\" target=\"_blank\" rel=\"noreferrer noopener\">Link to query<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>$MDC_Recommendations_SubAssessments = @()\n$pageSize = 1000\n$iteration = 0\n$searchParams = @{\n\t\t\t\t\tQuery = \"SecurityResources `\n\t\t\t\t\t\t\t| where type == 'microsoft.security\/assessments\/subassessments'\n\t\t\t\t\t\t\t| extend AssessmentKey = extract('.*assessments\/(.+?)\/.*',1,  id)\n\t\t\t\t\t\t\t| project AssessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId\n\t\t\t\t\t\t\t| extend SubAssessDescription = properties.description,\n\t\t\t\t\t\t\t\t\tSubAssessDisplayName = properties.displayName,\n\t\t\t\t\t\t\t\t\tSubAssessResourceId = properties.resourceDetails.id,\n\t\t\t\t\t\t\t\t\tSubAssessResourceSource = properties.resourceDetails.source,\n\t\t\t\t\t\t\t\t\tSubAssessCategory = properties.category,\n\t\t\t\t\t\t\t\t\tSubAssessSeverity = properties.status.severity,\n\t\t\t\t\t\t\t\t\tSubAssessCode = properties.status.code,\n\t\t\t\t\t\t\t\t\tSubAssessTimeGenerated = properties.timeGenerated,\n\t\t\t\t\t\t\t\t\tSubAssessRemediation = properties.remediation,\n\t\t\t\t\t\t\t\t\tSubAssessImpact = properties.impact,\n\t\t\t\t\t\t\t\t\tSubAssessVulnId = properties.id,\n\t\t\t\t\t\t\t\t\tSubAssessMoreInfo = properties.additionalData,\n\t\t\t\t\t\t\t\t\tSubAssessMoreInfoAssessedResourceType = properties.additionalData.assessedResourceType,\n\t\t\t\t\t\t\t\t\tSubAssessMoreInfoData = properties.additionalData.data `\n\t\t\t\t\t\t\t| join kind=leftouter (resourcecontainers | where type=='microsoft.resources\/subscriptions' | project SubName=name, subscriptionId) on subscriptionId \"\n\t\t\t\t\t\tFirst = $pageSize\n\t\t\t\t\t}\n\n$results = do {\n\t$iteration += 1\n\tWrite-Verbose \"Iteration #$iteration\" -Verbose\n\t$pageResults = Search-AzGraph  @searchParams -ManagementGroup $Global:ManagementGroupScope\n\t$searchParams.Skip += $pageResults.Count\n\t$MDC_Recommendations_SubAssessments += $pageResults\n} while ($pageResults.Count -eq $pageSize)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Reporting-script | Views (samples)<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">Below you can find sample views from the Excel spreadsheet. Data is coming from a small test environment with a few subscriptions and resources. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Try it out in your own environment to see your own data \ud83d\ude42<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_SUBASSESS_OTHER<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1271\" height=\"1075\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_OTHER.jpg\" alt=\"\" class=\"wp-image-895\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_OTHER.jpg 1271w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_OTHER-300x254.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_OTHER-1024x866.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_OTHER-768x650.jpg 768w\" sizes=\"auto, (max-width: 1271px) 100vw, 1271px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_SUBASSESS_IDENTITY<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"169\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_IDENTITY.jpg\" alt=\"\" class=\"wp-image-896\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_IDENTITY.jpg 630w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_SUBASSESS_IDENTITY-300x80.jpg 300w\" sizes=\"auto, (max-width: 630px) 100vw, 630px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_RBAC_ROLEDEF<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1334\" height=\"1072\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_ROLEDEF.jpg\" alt=\"\" class=\"wp-image-897\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_ROLEDEF.jpg 1334w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_ROLEDEF-300x241.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_ROLEDEF-1024x823.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_ROLEDEF-768x617.jpg 768w\" sizes=\"auto, (max-width: 1334px) 100vw, 1334px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_RBAC_DIRECT_SUB<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"608\" height=\"883\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_DIRECT_SUB.jpg\" alt=\"\" class=\"wp-image-893\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_DIRECT_SUB.jpg 608w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_DIRECT_SUB-207x300.jpg 207w\" sizes=\"auto, (max-width: 608px) 100vw, 608px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_CATEGORY_DATA<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"819\" height=\"621\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_DATA.jpg\" alt=\"\" class=\"wp-image-898\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_DATA.jpg 819w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_DATA-300x227.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_DATA-768x582.jpg 768w\" sizes=\"auto, (max-width: 819px) 100vw, 819px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_CATEGORY_NETWORKING<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"702\" height=\"314\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING.jpg\" alt=\"\" class=\"wp-image-899\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING.jpg 702w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_NETWORKING-300x134.jpg 300w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_RBAC_SCOPE_DELEGATION<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"573\" height=\"1074\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_SCOPE_DELEGATION.jpg\" alt=\"\" class=\"wp-image-900\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_SCOPE_DELEGATION.jpg 573w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_SCOPE_DELEGATION-160x300.jpg 160w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_SCOPE_DELEGATION-546x1024.jpg 546w\" sizes=\"auto, (max-width: 573px) 100vw, 573px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_CATEGORY_SUBLEVEL<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"840\" height=\"237\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_SUBLEVEL-1.jpg\" alt=\"\" class=\"wp-image-891\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_SUBLEVEL-1.jpg 840w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_SUBLEVEL-1-300x85.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_SUBLEVEL-1-768x217.jpg 768w\" sizes=\"auto, (max-width: 840px) 100vw, 840px\" \/><figcaption class=\"wp-element-caption\">Prioritize recommendations based on Category and RecommendationSeverity<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_CATEGORY_COMPUTE<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"541\" height=\"342\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_COMPUTE-1.jpg\" alt=\"\" class=\"wp-image-890\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_COMPUTE-1.jpg 541w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_COMPUTE-1-300x190.jpg 300w\" sizes=\"auto, (max-width: 541px) 100vw, 541px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_RESOURCETYPE<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"725\" height=\"961\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RESOURCETYPE.jpg\" alt=\"\" class=\"wp-image-888\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RESOURCETYPE.jpg 725w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RESOURCETYPE-226x300.jpg 226w\" sizes=\"auto, (max-width: 725px) 100vw, 725px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_CATEGORY_RESOURCELEVEL<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"721\" height=\"927\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_RESOURCELEVEL-1.jpg\" alt=\"\" class=\"wp-image-889\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_RESOURCELEVEL-1.jpg 721w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_CATEGORY_RESOURCELEVEL-1-233x300.jpg 233w\" sizes=\"auto, (max-width: 721px) 100vw, 721px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PT_RBAC_MG<\/h3>\n\n\n\n<figure class=\"wp-block-image size-full is-style-default\"><img loading=\"lazy\" decoding=\"async\" width=\"505\" height=\"1076\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1.jpg\" alt=\"\" class=\"wp-image-892\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1.jpg 505w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1-141x300.jpg 141w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/PT_RBAC_MG-1-481x1024.jpg 481w\" sizes=\"auto, (max-width: 505px) 100vw, 505px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Structure | Excel | Tables<\/h1>\n\n\n\n<figure class=\"wp-block-table alignwide has-small-font-size\"><table><tbody><tr><td>TableName<\/td><td>Purpose<\/td><td>Source<\/td><\/tr><tr><td>Unhealthy_All<\/td><td>Show All Unhealthy recommendations<\/td><td>Defender for Cloud<br>Extracted via Azure Resource Graph<\/td><\/tr><tr><td>Unhealthy_High<\/td><td>Show all Unhealthy recommendations with High priority<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Medium<\/td><td>Show all Unhealthy recommendations with medium priority<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Low<\/td><td>Show all Unhealthy recommendations with Low priority<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_All_SubLevel<\/td><td>Show all Unhealthy recommendations where the target is on subcription-level<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_All_ResourceLevel<\/td><td>Show all Unhealthy recommendations where the target is on resource-level<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_Networking<\/td><td>Show all Unhealthy recommendations related to Networking category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_AppServices<\/td><td>Show all Unhealthy recommendations related to AppServices category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_Compute<\/td><td>Show all Unhealthy recommendations related to Compute category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_Container<\/td><td>Show all Unhealthy recommendations related to Container category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_Data<\/td><td>Show all Unhealthy recommendations related to Data category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_IoT<\/td><td>Show all Unhealthy recommendations related to IoT category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>Unhealthy_Category_Id_Access<\/td><td>Show all Unhealthy recommendations related to IdentityAndAccess category<\/td><td>Filtered view of Unhealthy_All<\/td><\/tr><tr><td>SubAssess_All<\/td><td>Show all detailed information (SubAssessments)<\/td><td>Defender for Cloud<br>Extracted via Azure Resource Graph<\/td><\/tr><tr><td>SubAssess_Identity<\/td><td>Show all identity detailed information (SubAssessments)<\/td><td>Filtered view of SubAssess_All<\/td><\/tr><tr><td>SubAssess_Other<\/td><td>Show all other, non-identity detailed information (SubAssessments)<\/td><td>Filtered view of SubAssess_All<\/td><\/tr><tr><td>RBAC_RoleAssignments<\/td><td>Show all Azure RBAC Role Assignments<\/td><td>Azure Role Assignsment<\/td><\/tr><tr><td>RBAC_Direct_Sublevel<\/td><td>Show all Azure RBAC Role Assignments directly on Sub-level (not part of group)<\/td><td>Filtered of RBAC_RoleAssignments<\/td><\/tr><tr><td>RBAC_Direct_Mglevel<\/td><td>Show all Azure RBAC Role Assignments directly on Mg-level (not part of group)<\/td><td>Filtered of RBAC_RoleAssignments<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading alignwide\">Structure | Excel | Pivot tables<\/h1>\n\n\n\n<figure class=\"wp-block-table alignfull has-small-font-size\"><table><tbody><tr><td>Pivot table name<\/td><td>Purpose<\/td><td>Source Table<\/td><td>Sort Order<\/td><\/tr><tr><td>PT_CATEGORY_SUBLEVEL<\/td><td>Prioritize recommendations based on Category and RecommendationSeverity<\/td><td>Unhealthy_All_SubLevel<\/td><td>(1) Category (Storage, Network, Identity, etc.)<br>(2) RecommendationSeverity (High, Medium, Low)<br>(3) RecommendationName<br>(4) SubName (suscription name)<\/td><\/tr><tr><td>PT_CATEGORY_RESOURCELEVEL<\/td><td>Prioritize recommendations based on ResourceType and RecommendationSeverity<\/td><td>Unhealthy_All_ResourceLevel<\/td><td>(1) Category (Storage, Network, Identity, etc.)<br>(2) RecommendationSeverity (High, Medium, Low)<br>(3) RecommendationName<br>(4) SubName (suscription name)<br>(5) ResourceRgName<br>(6) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_NETWORKING<\/td><td>Prioritize networking recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_Networking<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_APPSERVICES<\/td><td>Prioritize AppServices recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_AppServices<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_COMPUTE<\/td><td>Prioritize Compute recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_Compute<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_CONTAINER<\/td><td>Prioritize Container recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_Container<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_DATA<\/td><td>Prioritize Data recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_Data<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_ID_ACCESS<\/td><td>Prioritize Identity and Access recommendations based on RecommendationSeverity<\/td><td>Unhealthy_Category_IdentityAndAccess<\/td><td>(1) RecommendationSeverity (High, Medium, Low)<br>(2) RecommendationName<br>(3) SubName (suscription name)<br>(4) ResourceRgName<br>(5) ResourceName<\/td><\/tr><tr><td>PT_RESOURCETYPE<\/td><td>Prioritize recommendations based on ResourceType and RecommendationSeverity<\/td><td>Unhealthy_All_ResourceLevel<\/td><td>(1) ResourceType (SQL, Keyvault, Storage, Network, Identity, etc.)<br>(2) RecommendationSeverity (High, Medium, Low)<br>(3) RecommendationName<br>(4) SubName (suscription name)<br>(5) ResourceRgName<br>(6) ResourceName<\/td><\/tr><tr><td>PT_CATEGORY_RESOURCELEVEL<\/td><td>Prioritize recommendations based on Category and RecommendationSeverity<\/td><td>Unhealthy_All_ResourceLevel<\/td><td>(1) Category (Storage, Network, Identity, etc.)<br>(2) RecommendationSeverity (High, Medium, Low)<br>(3) RecommendationName<br>(4) SubName (suscription name)<br>(5) ResourceRgName<br>(6) ResourceName<\/td><\/tr><tr><td>PT_SUBASSESS_IDENTITY<\/td><td>Prioritize recommendations based on SubAssessmentSeverity (identity-related)<\/td><td>SubAssess_Identity<\/td><td>(1) SubAssessSeverity (High, Medium, Low)<br>(2) SubAssessDisplayName (recommendation)<br>(3) SubAssessResDisplayName (resource)<br>(4) SubAssessResUserPrincipalName (resource)<br>(5) SubName (suscription name)<\/td><\/tr><tr><td>PT_SUBASSESS_OTHER<\/td><td>Prioritize recommendations based on SubAssessmentSeverity (non-identity related)<\/td><td>SubAssess_Other<\/td><td>(1) SubAssessSeverity (High, Medium, Low)<br>(2) SubAssessDisplayName (recommendation)<br>(3) SubAssessResDisplayName (resource)<br>(4) SubAssessResUserPrincipalName (resource)<br>(5) SubName (suscription name)<\/td><\/tr><tr><td>PT_RBAC_ROLEDEF<\/td><td>Detail RBAC permissions, sorted by RoleDefinitionName and Scope_Delegation<\/td><td>RBAC_RoleAssignments<\/td><td>(1) SubscriptionName<br>(2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)<br>(3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)<br>(4) Scope (management group name or \/)<br>(5) RBAC_Delegation_Type (Group_inheritance, Direct)<br>(6) RBAC_GroupName<br>(7) ObjectType<br>(8) DisplayName<br>(9) UserPrincipalName<\/td><\/tr><tr><td>PT_RBAC_SCOPE_DELEGATION<\/td><td>Detail RBAC permissions, sorted by Scope_Delegation and RoleDefinitionName<\/td><td>RBAC_RoleAssignments<\/td><td>(1) SubscriptionName<br>(2) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)<br>(3) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)<br>(4) Scope (management group name or \/)<br>(5) RBAC_Delegation_Type (Group_inheritance, Direct)<br>(6) RBAC_GroupName<br>(7) ObjectType<br>(8) DisplayName<br>(9) UserPrincipalName<\/td><\/tr><tr><td>PT_RBAC_MG<\/td><td>Detect direct RBAC permissions on Mg-level (not done by group)<\/td><td>RBAC_Direct_Mglevel<\/td><td>(1) SubscriptionName<br>(2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)<br>(3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)<br>(4) Scope (management group name or \/)<br>(5) ObjectType<br>(6) DisplayName<br>(7) UserPrincipalName<\/td><\/tr><tr><td>PT_RBAC_SUB<\/td><td>Detect direct RBAC permissions on Sub-level (not done by group)<\/td><td>RBAC_Direct_Sublevel<\/td><td>(1) SubscriptionName<br>(2) RoleDefinitionName (Contributor, Owner, Cost Management Reader, etc.)<br>(3) Scope_Delegation (Direct_SUB, Direct_RG, Inheritance_MG)<br>(4) Scope (management group name or \/)<br>(5) ObjectType<br>(6) DisplayName<br>(7) UserPrincipalName<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Script flow overview<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">The script flow is:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>(1) Connect to Azure<\/td><\/tr><tr><td>(2) Collect Azure Role Assignments<\/td><\/tr><tr><td>(3) Collect Microsoft Defender for Cloud recommendations via Azure Resource Graph<br><br>* MDC | Recommendations with link<br>* MDC | SubAssessments (Detailed Infomation)<br>* SubAssessment Identity Lookup<\/td><\/tr><tr><td>(4) Prepare different filtering-variables<\/td><\/tr><tr><td>(5) Delete existing report file, if found<\/td><\/tr><tr><td>(6) Build Introduction table &#8211; by reading content from content.csv file (can be adjusted). Includes special formatting<\/td><\/tr><tr><td>(7) Export tables to Excel<\/td><\/tr><tr><td>(8) Build pivot tables array &#8211; including spec<\/td><\/tr><tr><td>(9) Build pivots from array<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Implementation of script<\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Account<\/td><td>You need to create a service principal &#8211; or use your own account for testing<br><br>Account must be having READ permissions on the level, where you want to report the status from (typically tenant or management group-level)<\/td><\/tr><tr><td>Powershell<\/td><td>Script uses the following 4 Powershell modules:<br><br><strong>AzureAD<\/strong> (used to lookup Azure AD object information)<br><br><strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/azure\/new-azureps-module-az?view=azps-9.3.0\" target=\"_blank\" rel=\"noreferrer noopener\">Az.*<\/a><\/strong> &#8211; used to connect to Azure<br><br><strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/az.resourcegraph\/?view=azps-9.3.0\" target=\"_blank\" rel=\"noreferrer noopener\">Az.ResourceGraph<\/a><\/strong> &#8211; used to connect to Azure Resource Graph and run queries<br><br><strong><a href=\"https:\/\/github.com\/dfinke\/ImportExcel\" target=\"_blank\" rel=\"noreferrer noopener\">ImportExcel<\/a><\/strong> &#8211; used to export data in Excel file<br>Big thanx to Microsoft MVP <a href=\"https:\/\/twitter.com\/dfinke\" target=\"_blank\" rel=\"noreferrer noopener\">Doug Finke<\/a> for creating a fantastic powershell module for Excel.<\/td><\/tr><tr><td>Variables<\/td><td>You can customize the script header for your needs &#8211; including scoping via management groups &#8211; and exclude subscriptions, if needed.<br><br>See below<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code># Scope (MG)\n# You can define the scope for the targeting, supporting management groups or tenant root id (all subs)\n\n$Global:ManagementGroupScope  = \"xxxxxx\"  # can mg e.g. mg-company or AAD Id (=Tenant Root Id)\n\n# Exclude list\n# You can exclude certain subs, resource groups, resources, if you don't want to have them as part of the scope\n\n$global:Exclude_Subscriptions  = @(\"xxxxxxxxxxxxxxxxxxxxxx\") # for example platform-connectivity\n$global:Exclude_ResourceGroups = @()\n$global:Exclude_Resource = @()\n$global:Exclude_Resource_Contains = @()\n$global:Exclude_Resource_Startswith = @()\n$global:Exclude_Resource_Endswith = @()\n\n# Content file - can be found on Github\n$HelpContentFile = \"C:\\SCRIPTS\\Azure-Recommendations-Get-In-Control\\Content.csv\"\n\n# OutputFile\n$FileOutput = \"C:\\SCRIPTS\\Azure-Recommendations-Get-In-Control\\Azure_Recommendations_Get-in-Control.xlsx\"\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Tips &amp; Tricks<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Get started introduction (overview)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">As part of the build process, all tables, pivots tables, information about sort order, etc. will be added in the first table &#8216;Introduction_Help&#8217;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Content is read from the content.csv file. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Feel free to add more columns like Recommended Actions, Frequency, Responsible Team\/person to fit your needs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Navigation-view in Excel<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In order to see all tabs, you can activate <strong>Navigation view<\/strong> in Excel<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1778\" height=\"1390\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1.jpg\" alt=\"\" class=\"wp-image-902\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1.jpg 1778w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1-300x235.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1-1024x801.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1-768x600.jpg 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Navigation-1-1536x1201.jpg 1536w\" sizes=\"auto, (max-width: 1778px) 100vw, 1778px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Filtering<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Filtering capability is automatically activated as part of deployment<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1367\" height=\"575\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Filtering.jpg\" alt=\"\" class=\"wp-image-903\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Filtering.jpg 1367w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Filtering-300x126.jpg 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Filtering-1024x431.jpg 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Filtering-768x323.jpg 768w\" sizes=\"auto, (max-width: 1367px) 100vw, 1367px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sort-order in Pivot<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Feel free to adjust the sort-order to your needs. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"332\" height=\"1024\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Pivot-Order-332x1024.jpg\" alt=\"\" class=\"wp-image-905\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Pivot-Order-332x1024.jpg 332w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Pivot-Order-97x300.jpg 97w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/Pivot-Order.jpg 374w\" sizes=\"auto, (max-width: 332px) 100vw, 332px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Background Recently, I was asked to build a simple reporting-script, which integrates data from Microsoft Defender for Cloud and Azure &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"Automate Reporting of Defender for Cloud recommendations &#038; Role Assignments with 35 different views\" class=\"read-more button\" href=\"https:\/\/mortenknudsen.net\/?p=882#more-882\" aria-label=\"Read more about Automate Reporting of Defender for Cloud recommendations &#038; Role Assignments with 35 different views\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":922,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"ngg_post_thumbnail":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[55,57,48,60],"tags":[18,41,87,86,80,17],"class_list":["post-882","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-azure-security","category-defender-for-cloud","category-scripting","tag-azure","tag-defender","tag-defenderforcloud","tag-mdc","tag-microsoftsecurity","tag-security","infinite-scroll-item","resize-featured-image"],"featured_image_src":"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1.jpg","author_info":{"display_name":"Morten Knudsen","author_link":"https:\/\/mortenknudsen.net\/?author=1"},"jetpack_featured_media_url":"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2023\/01\/RoleAssign-1.jpg","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/882","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=882"}],"version-history":[{"count":22,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions"}],"predecessor-version":[{"id":2332,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions\/2332"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/media\/922"}],"wp:attachment":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}