analyzer/2_wara_data_analyzer.ps1
|
#Requires -Version 7 <# .SYNOPSIS Well-Architected Reliability Assessment Script .DESCRIPTION The script "2_wara_data_analyzer" will process the JSON file created by the "1_wara_collector" script and will create the core WARA Action Plan Excel file. .PARAMETER Debugging Switch to enable debugging mode. .PARAMETER Help Switch to display help information. .PARAMETER RepoUrl Specifies the git repository URL that contains APRL contents if you want to use custom APRL repository. .PARAMETER JSONFile Path to the JSON file created by the "1_wara_collector" script. .EXAMPLE .\2_wara_data_analyzer.ps1 -JSONFile 'C:\Temp\WARA_File_2024-04-01_10_01.json' -Debugging .LINK https://github.com/Azure/Azure-Proactive-Resiliency-Library-v2 #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingWriteHost', '', Justification = 'False positive as Write-Host does not represent a security risk and this script will always run on host consoles')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSReviewUnusedParameter', '', Justification = 'False positive as parameters are not always required')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments','', Justification='Variable is reserved for future use')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidGlobalVars','', Justification='This will be fixed in refactor')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseSingularNouns','', Justification='This will be fixed in refactor')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions','', Justification='This will be fixed in refactor')] Param( [switch]$Debugging, [switch]$Help, [string]$CustomRecommendationsYAMLPath, [ValidatePattern('^https:\/\/.+$')] [string]$RepoUrl = 'https://github.com/Azure/Azure-Proactive-Resiliency-Library-v2', [Parameter(mandatory = $true)] [string] $JSONFile) # Checking the operating system running this script. if (-not $IsWindows) { Write-Host 'This script only supports Windows operating systems currently. Please try to run with Windows operating systems.' Exit } if ($Debugging.IsPresent) { $DebugPreference = 'Continue' } else { $DebugPreference = 'silentlycontinue' } $Script:FilterRecommendations = $true $Script:Runtime = Measure-Command -Expression { function Get-HelpMessage { Write-Host '' Write-Host 'Parameters' Write-Host '' Write-Host " -JSONFile : Mandatory; WARA JSON file generated by '1_wara_collector.ps1' script. " Write-Host ' -Debugging : Switch; Writes Debugging information of the script during the execution. ' Write-Host " -CustomRecommendationsYAMLPath : Optional; Path to a custom YAML file with recommendations. " Write-Host ' -RepoUrl : Optional; Specifies the git repository URL that contains APRL contents if you want to use non-standard APRL repository.' Write-Host '' Write-Host 'Examples: ' Write-Host ' Run using JSON file' Write-Host " .\2_wara_analyzer.ps1 -JSONFile 'C:\Temp\WARA_File_2024-04-01_10_01.json'" Write-Host '' Write-Host ' Run using JSON file with Debugging details' Write-Host " .\2_wara_analyzer.ps1 -JSONFile 'C:\Temp\WARA_File_2024-04-01_10_01.json' -Debugging" Write-Host '' Write-Host '' } function Test-Requirement { # Install required modules Write-Host 'Validating ' -NoNewline Write-Host 'ImportExcel' -ForegroundColor Cyan -NoNewline Write-Host ' Module..' $ImportExcel = Get-Module -Name ImportExcel -ListAvailable -ErrorAction silentlycontinue if ($null -eq $ImportExcel) { Write-Host 'Installing ImportExcel Module' -ForegroundColor Yellow Install-Module -Name ImportExcel -Force -SkipPublisherCheck } Write-Host 'Validating ' -NoNewline Write-Host 'Powershell-YAML' -ForegroundColor Cyan -NoNewline Write-Host ' Module..' $AzModules = Get-Module -Name powershell-yaml -ListAvailable -ErrorAction silentlycontinue if ($null -eq $AzModules) { Write-Host 'Installing Az Modules' -ForegroundColor Yellow Install-Module -Name powershell-yaml -SkipPublisherCheck -InformationAction SilentlyContinue } Write-Host 'Validating ' -NoNewline Write-Host 'Git' -ForegroundColor Cyan -NoNewline Write-Host ' Installation..' $GitVersion = git --version if ($null -eq $GitVersion) { Write-Host 'Missing Git' -ForegroundColor Red Exit } } function Get-RepoVersion { param( [Parameter(Mandatory = $true)] [string] $ClonePath ) return Get-Content -Path "$ClonePath\tools\Version.json" -ErrorAction SilentlyContinue | ConvertFrom-Json } function Set-RecommendationControl { param ( [string]$category ) switch ($category) { 'BusinessContinuity' { return 'Business Continuity' } 'DisasterRecovery' { return 'Disaster Recovery' } 'MonitoringAndAlerting' { return 'Monitoring And Alerting' } 'ServiceUpgradeAndRetirement' { return 'Service Upgrade And Retirement' } 'OtherBestPractices' { return 'Other Best Practices' } 'HighAvailability' { return 'High Availability' } default { return $category } } } function Set-LocalFile { [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Low')] param() if ($PSCmdlet.ShouldProcess('')) { # Clone the GitHub repository to a temporary folder # Define script path as the default path to save files $workingFolderPath = Get-Location Set-Location -Path $workingFolderPath; $Script:clonePath = "$workingFolderPath\Azure-Proactive-Resiliency-Library-v2" if ((Get-ChildItem -Path $Script:clonePath -Force | Measure-Object).Count -gt 0) { Write-Debug 'APRL Folder does exist. Reseting it...' Get-Item -Path $Script:clonePath | Remove-Item -Recurse -Force git clone $RepoUrl $Script:clonePath --quiet } else { git clone $RepoUrl $Script:clonePath --quiet } Write-Debug 'Checking the version of the script' $RepoVersion = Get-RepoVersion -ClonePath $Script:clonePath if ($Script:Version -ne $RepoVersion.Analyzer) { Write-Host 'This version of the script is outdated. ' -BackgroundColor DarkRed Write-Host 'Please use a more recent version of the script.' -BackgroundColor DarkRed } else { Write-Host 'This version of the script is current version. ' -BackgroundColor DarkGreen } } } function Convert-JSON { Write-Host 'Processing JSON File' # Load the JSON file from the collector script... $JSONFile = Get-Item -Path $JSONFile $JSONFile = $JSONFile.FullName $results = Get-Content -Path $JSONFile | ConvertFrom-Json $Script:AllResourceTypesOrdered = $results.ResourceType $Script:Outages = $results.Outages $Script:SupportTickets = $results.SupportTickets $Script:Retirements = $results.Retirements $Script:ServiceHealth = $results.ServiceHealth $Script:CollectorDetails = $results.ScriptDetails $Script:OutOfScope = $results.OutOfScope $RepoVersion = $RepoVersion = Get-RepoVersion -ClonePath $Script:clonePath if ($Script:CollectorDetails.Version -eq $RepoVersion.Collector) { Write-Host 'The JSON file was created by the current version of the Collector Script. ' -BackgroundColor DarkGreen } else { Write-Host "The JSON file was created by an outdated version ($($Script:CollectorDetails.Version)) of the Collector Script. The latest version is $($RepoVersion.Collector)" -BackgroundColor DarkRed } $CoreResources = $results.ImpactedResources $CoreAdvisories = $results.Advisory $Script:ServicesYAML = Get-ChildItem -Path ($Script:clonePath + '\azure-resources') -Filter 'recommendations.yaml' -Recurse $Script:WAFYAML = Get-ChildItem -Path ($Script:clonePath + '\azure-waf') -Filter 'recommendations.yaml' -Recurse if ($Script:CollectorDetails.SAP -eq 'True') { $Script:ServicesYAML += Get-ChildItem -Path ($Script:clonePath + '\azure-specialized-workloads\sap') -Filter 'recommendations.yaml' -Recurse } if ($Script:CollectorDetails.AVD -eq 'True') { $Script:ServicesYAML += Get-ChildItem -Path ($Script:clonePath + '\azure-specialized-workloads\avd') -Filter 'recommendations.yaml' -Recurse } if ($Script:CollectorDetails.AVS -eq 'True') { $Script:ServicesYAML += Get-ChildItem -Path ($Script:clonePath + '\azure-specialized-workloads\avs') -Filter 'recommendations.yaml' -Recurse } if ($Script:CollectorDetails.HPC -eq 'HPC') { $Script:ServicesYAML += Get-ChildItem -Path ($Script:clonePath + '\azure-specialized-workloads\hpc') -Filter 'recommendations.yaml' -Recurse } $Script:AdvisorContent = $CoreAdvisories | Select-Object -Property recommendationId, type, category, impact, description -Unique # Load custom YAML content if provided... # Custom YAML variable is always here regardless of whether a custom file is provided. $Script:CustomYAMLContent = @() # If a custom file is provided, load it and add it to the custom YAML content. if (![string]::IsNullOrWhiteSpace(($CustomRecommendationsYAMLPath))) { $Script:CustomYAMLContent = Get-Content -Path $CustomRecommendationsYAMLPath | ConvertFrom-Yaml } $Script:ServicesYAMLContent = @() foreach ($YAML in $Script:ServicesYAML) { if (![string]::IsNullOrEmpty($YAML)) { $Script:ServicesYAMLContent += Get-Content -Path $YAML | ConvertFrom-Yaml } } $Script:WAFYAMLContent = @() foreach ($YAML in $Script:WAFYAML) { if (![string]::IsNullOrEmpty($YAML)) { $Script:WAFYAMLContent += Get-Content -Path $YAML | ConvertFrom-Yaml } } $Script:MergedRecommendation = @() foreach ($Recom in $CoreResources | Where-Object { $_ -ne $null }) { if ($($Recom.checkName) -and $($Recom.selector)) { # This is a runbook recommendation... $recomContent = $Script:CustomYAMLContent ` | Where-Object { ($_.aprlGuid -eq $Recom.recommendationId) -and ($_.checkName -eq $Recom.checkName) } ` | Select-Object -First 1 if (-not $recomContent) { # If we couldn't find a check-specific recommendation, try to find a generic one... $recomContent = $Script:CustomYAMLContent ` | Where-Object { ($_.aprlGuid -eq $Recom.recommendationId) } ` | Select-Object -First 1 } if (-not $recomContent) { # If we couldn't find a check-specific recommendation, try to find a generic one... $recomContent = $Script:ServicesYAMLContent ` | Where-Object { ($_.aprlGuid -eq $Recom.recommendationId) } ` | Select-Object -First 1 } if (-not $recomContent) { # If we still couldn't find a recommendation, create a default one.. $recomContent = [pscustomobject]@{ description = [string]::Empty recommendationResourceType = 'Unknown' recommendationImpact = 'Unknown' } } $tmp = @{ 'How was the resource/recommendation validated or what actions need to be taken?' = $Recom.validationAction; recommendationId = $Recom.recommendationId; recommendationTitle = $recomContent.description; resourceType = $recomContent.recommendationResourceType; impact = $recomContent.recommendationImpact; subscriptionId = $Recom.subscriptionId; resourceGroup = $Recom.resourceGroup; name = $Recom.name; id = $Recom.id; location = $Recom.location; param1 = $Recom.param1; param2 = $Recom.param2; param3 = $Recom.param3; param4 = $Recom.param4; param5 = $Recom.param5; supportTicketId = [string]::Empty; source = $Recom.selector; checkName = $Recom.checkName; 'WAF Pillar' = 'Reliability'; tagged = $Recom.tagged } $Script:MergedRecommendation += $tmp } else { # This isn't a runbook recommendation... $RecomTitle = $Script:ServicesYAMLContent | Where-Object { $_.aprlGuid -eq $Recom.recommendationId } if ([string]::IsNullOrEmpty($RecomTitle.recommendationTypeId) -or (![string]::IsNullOrEmpty($RecomTitle.recommendationTypeId) -and $RecomTitle.recommendationTypeId -notin $CoreAdvisories.recommendationId)) { $Ticket = $Script:SupportTickets | Where-Object { $_.'Related Resource' -eq $Recom.id } if (($RecomTitle.recommendationMetadataState -eq 'Active') -or $Recom.validationAction -eq 'IMPORTANT - Recommendation cannot be validated with ARGs - Validate Resources manually' -or $Recom.validationAction -eq 'IMPORTANT - Query under development - Validate Resources manually' ) { $Tickets = if ($Ticket.'Ticket ID'.count -gt 1) { $Ticket.'Ticket ID' | ForEach-Object { $_ + ' /' } }else { $Ticket.'Ticket ID' } $Tickets = [string]$Tickets $Tickets = if ($Tickets -like '* /*') { $Tickets -replace '.$' }else { $Tickets } $tmp = @{ 'How was the resource/recommendation validated or what actions need to be taken?' = $Recom.validationAction; recommendationId = $Recom.recommendationId; recommendationTitle = $RecomTitle.description; resourceType = $RecomTitle.recommendationResourceType; impact = $RecomTitle.recommendationImpact; subscriptionId = $Recom.subscriptionId; resourceGroup = $Recom.resourceGroup; name = $Recom.name; id = $Recom.id; location = $Recom.location; param1 = $Recom.param1; param2 = $Recom.param2; param3 = $Recom.param3; param4 = $Recom.param4; param5 = $Recom.param5; supportTicketId = $Tickets; source = $Recom.selector; checkName = $Recom.checkName; 'WAF Pillar' = 'Reliability'; tagged = $Recom.tagged } $Script:MergedRecommendation += $tmp } elseif ($Recom.validationAction -eq 'IMPORTANT - Resource Type is not available in either APRL or Advisor - Validate Resources manually if Applicable, if not Delete this line' ) { $tmp = @{ 'How was the resource/recommendation validated or what actions need to be taken?' = $Recom.validationAction; recommendationId = ''; recommendationTitle = $RecomTitle.description; resourceType = $Recom.recommendationId; impact = ''; subscriptionId = $Recom.subscriptionId; resourceGroup = $Recom.resourceGroup; name = $Recom.name; id = $Recom.id; location = $Recom.location; param1 = $Recom.param1; param2 = $Recom.param2; param3 = $Recom.param3; param4 = $Recom.param4; param5 = $Recom.param5; supportTicketId = $Tickets; source = $Recom.selector; checkName = $Recom.checkName; 'WAF Pillar' = 'Reliability'; tagged = $Recom.tagged } $Script:MergedRecommendation += $tmp } } } } $Script:RecommendedAdv = @() foreach ($adv in $CoreAdvisories) { if (![string]::IsNullOrEmpty($adv.recommendationId)) { #$APRLADV = $Script:ServicesYAMLContent | Where-Object { $_.recommendationTypeId -eq $adv.recommendationId } #if ($APRLADV.recommendationTypeId -eq $adv.recommendationId ) { $Ticket = $Script:SupportTickets | Where-Object { $_.'Related Resource' -eq $adv.id } $Tickets = if ($Ticket.'Ticket ID'.count -gt 1) { $Ticket.'Ticket ID' | ForEach-Object { $_ + ' /' } }else { $Ticket.'Ticket ID' } $Tickets = [string]$Tickets $Tickets = if ($Tickets -like '* /*') { $Tickets -replace '.$' }else { $Tickets } $WAFPillar = if ($adv.category -eq 'HighAvailability') { 'Reliability' }else { $adv.category } $tmp = @{ 'How was the resource/recommendation validated or what actions need to be taken?' = 'Advisor - Queries'; recommendationId = $adv.recommendationId; recommendationTitle = $adv.description; impact = $adv.impact; resourceType = $adv.type; subscriptionId = $adv.subscriptionId; resourceGroup = $adv.resourceGroup; name = $adv.name; id = $adv.id; location = $adv.location; param1 = ''; param2 = ''; param3 = ''; param4 = ''; param5 = ''; supportTicketId = $Tickets; source = 'ADVISOR'; checkName = ''; 'WAF Pillar' = $WAFPillar; tagged = $true } $Script:MergedRecommendation += $tmp $Script:RecommendedAdv += $adv.recommendationId #} } } foreach ($WAF in $Script:WAFYAMLContent) { $tmp = @{ 'How was the resource/recommendation validated or what actions need to be taken?' = "IMPORTANT - Update this item based on Discovery Workshop Questionnaire"; recommendationId = [string]$WAF.aprlGuid; recommendationTitle = [string]$WAF.description; resourceType = [string]$WAF.recommendationResourceType; impact = [string]$WAF.recommendationImpact; subscriptionId = ''; resourceGroup = ''; name = 'Entire Workload'; id = ''; location = ''; param1 = ''; param2 = ''; param3 = ''; param4 = ''; param5 = ''; supportTicketId = ''; source = 'APRL'; checkName = '' 'WAF Pillar' = 'Reliability'; tagged = $true } $Script:MergedRecommendation += $tmp } } function Build-ExcelFile { Write-Host 'Starting Excel file Processing. ' $TableStyle = 'Light19' $Script:Recommendations = @() # Defines the Excel file to be created in the root folder $Script:ExcelFile = ($(Get-Location).Path + '\WARA Action Plan ' + (Get-Date -Format 'yyyy-MM-dd-HH-mm') + '.xlsx') function Add-ImpactedResource { #################### Creates the first sheet (ImpactedResources) $Styles1 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -Bold -BackgroundColor 'DarkSlateGray' -AutoSize -Range 'A1:S1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -AutoSize -NumberFormat '0' -Range 'A:B' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -Width 100 -WrapText -NumberFormat '0' -Range 'C:C' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -AutoSize -NumberFormat '0' -Range 'D:I' New-ExcelStyle -HorizontalAlignment Left -FontName 'Calibri' -FontSize 11 -Width 80 -Range 'J:J' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -AutoSize -NumberFormat '0' -Range 'K:S' ) $Styles2 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -Bold -BackgroundColor 'DarkSlateGray' -AutoSize -Range 'A1:G1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -AutoSize -NumberFormat '0' -Range 'A:F' New-ExcelStyle -HorizontalAlignment Left -FontName 'Calibri' -FontSize 11 -Width 80 -Range 'G:G' ) $cond = @() $cond += New-ConditionalText "IMPORTANT - Update this item based on Discovery Workshop Questionnaire" -Range A:A $cond += New-ConditionalText 'IMPORTANT' -Range A:A $cond2 = @() $cond2 += New-ConditionalText 'No Action Required' -Range A:A $ImpactedResourcesSheet = New-Object System.Collections.Generic.List[System.Object] $ImpactedResourcesSheet.Add('How was the resource/recommendation validated or what actions need to be taken?') $ImpactedResourcesSheet.Add('resourceType') $ImpactedResourcesSheet.Add('recommendationTitle') $ImpactedResourcesSheet.Add('recommendationId') $ImpactedResourcesSheet.Add('impact') $ImpactedResourcesSheet.Add('subscriptionId') $ImpactedResourcesSheet.Add('resourceGroup') $ImpactedResourcesSheet.Add('location') $ImpactedResourcesSheet.Add('name') $ImpactedResourcesSheet.Add('id') $ImpactedResourcesSheet.Add('param1') $ImpactedResourcesSheet.Add('param2') $ImpactedResourcesSheet.Add('param3') $ImpactedResourcesSheet.Add('param4') $ImpactedResourcesSheet.Add('param5') $ImpactedResourcesSheet.Add('supportTicketId') $ImpactedResourcesSheet.Add('source') $ImpactedResourcesSheet.Add('WAF Pillar') $ImpactedResourcesSheet.Add('checkName') $OutOfScopeSheet = New-Object System.Collections.Generic.List[System.Object] $OutOfScopeSheet.Add('description') $OutOfScopeSheet.Add('type') $OutOfScopeSheet.Add('subscriptionId') $OutOfScopeSheet.Add('resourceGroup') $OutOfScopeSheet.Add('name') $OutOfScopeSheet.Add('location') $OutOfScopeSheet.Add('id') $Script:MergedRecommendation | ForEach-Object { [PSCustomObject]$_ } | Select-Object $ImpactedResourcesSheet | Export-Excel -Path $ExcelFile -WorksheetName 'ImpactedResources' -TableName 'Table2' -ConditionalText $cond -AutoSize -TableStyle $TableStyle -Style $Styles1 $Script:OutOfScope | ForEach-Object { [PSCustomObject]$_ } | Select-Object $OutOfScopeSheet | Export-Excel -Path $ExcelFile -WorksheetName 'Other-OutOfScope' -TableName 'UnTagTable' -ConditionalText $cond2 -AutoSize -TableStyle $TableStyle -Style $Styles2 } function Add-ResourceType { #################### Creates the second sheet (ResourceTypes) $ResourceTypeSheet = New-Object System.Collections.Generic.List[System.Object] $ResourceTypeSheet.Add('Resource Type') $ResourceTypeSheet.Add('Number of Resources') $ResourceTypeSheet.Add('Available in APRL/ADVISOR?') $ResourceTypeSheet.Add('Assessment Owner') $ResourceTypeSheet.Add('Status') $ResourceTypeSheet.Add('Notes') $TypeStyle = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -Bold -BackgroundColor 'DarkSlateGray' -AutoSize -Range 'A1:F1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -AutoSize -NumberFormat '0' -Range 'A:F' ) $Script:AllResourceTypesOrdered | ForEach-Object { [PSCustomObject]$_ } | Select-Object $ResourceTypeSheet | Export-Excel -Path $ExcelFile -WorksheetName 'ResourceTypes' -TableName 'TableTypes' -AutoSize -TableStyle $TableStyle -Style $TypeStyle } function Add-Outage { #################### Creates the Outages sheet $Script:OutagesSheet = @() $RealOutages = $Script:Outages | Where-Object { $_.properties.description -like '*How can customers make incidents like this less impactful?*' -and $_.properties.impactStartTime -gt ((Get-Date).AddMonths(-3)) } foreach ($Outage in $RealOutages) { if (![string]::IsNullOrEmpty($Outage.name)) { try { $HTML = New-Object -Com 'HTMLFile' $HTML.write([ref]$Outage.properties.description) $OutageDescription = $Html.body.innerText $SplitDescription = $OutageDescription.split('How can we make our incident communications more useful?').split('How can customers make incidents like this less impactful?').split('How are we making incidents like this less likely or less impactful?').split('How did we respond?').split('What went wrong and why?').split('What happened?') $whathap = ($SplitDescription[1]).Split([Environment]::NewLine)[1] $whatwent = ($SplitDescription[2]).Split([Environment]::NewLine)[1] $howdid = ($SplitDescription[3]).Split([Environment]::NewLine)[1] $howarewe = ($SplitDescription[4]).Split([Environment]::NewLine)[1] $howcan = ($SplitDescription[5]).Split([Environment]::NewLine)[1] } catch { $whathap = "" $whatwent = "" $howdid = "" $howarewe = "" $howcan = "" } $OutProps = $Outage.properties $tmp = @{ 'Tracking ID' = [string]$Outage.name; 'Event Type' = [string]$OutProps.eventType; 'Event Source' = [string]$OutProps.eventSource; 'Status' = [string]$OutProps.status; 'Title' = [string]$OutProps.title; 'Level' = [string]$OutProps.level; 'Event Level' = [string]$OutProps.eventLevel; 'Start Time' = [string]$OutProps.impactStartTime; 'Mitigation Time' = [string]$OutProps.impactMitigationTime; 'Impacted Service' = [string]$OutProps.impact.impactedService; 'What happened' = $whathap; 'What went wrong and why' = $whatwent; 'How did we respond' = $howdid; 'How are we making incidents like this less likely or less impactful' = $howarewe; 'How can customers make incidents like this less impactful' = $howcan; } $Script:OutagesSheet += $tmp } } $Styles3 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 14 -Range 'A1:B1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 18 -Range 'C1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 20 -Range 'D1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 55 -Range 'E1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 20 -Range 'F1:I1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 25 -Range 'J1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 80 -Range 'K1:O1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -VerticalAlignment Center -WrapText -Range 'A:O' ) # Configure the array of fields to be used in the Recommendations sheet $OutagesWorksheet = New-Object System.Collections.Generic.List[System.Object] $OutagesWorksheet.Add('Tracking ID') $OutagesWorksheet.Add('Event Type') $OutagesWorksheet.Add('Event Source') $OutagesWorksheet.Add('Status') $OutagesWorksheet.Add('Title') $OutagesWorksheet.Add('Level') $OutagesWorksheet.Add('Event Level') $OutagesWorksheet.Add('Start Time') $OutagesWorksheet.Add('Mitigation Time') $OutagesWorksheet.Add('Impacted Service') $OutagesWorksheet.Add('What happened') $OutagesWorksheet.Add('What went wrong and why') $OutagesWorksheet.Add('How did we respond') $OutagesWorksheet.Add('How are we making incidents like this less likely or less impactful') $OutagesWorksheet.Add('How can customers make incidents like this less impactful') if (![string]::IsNullOrEmpty($Script:OutagesSheet)) { $Script:OutagesSheet | ForEach-Object { [PSCustomObject]$_ } | Select-Object $OutagesWorksheet | Export-Excel -Path $ExcelFile -WorksheetName 'Outages' -TableName 'TableOutage' -AutoSize -TableStyle $tableStyle -Style $Styles3 } } function Add-Retirement { #################### Creates the Retirement sheet $Script:RetirementSheet = @() foreach ($Retires in $Script:Retirements) { if (![string]::IsNullOrEmpty($Retires)) { $HTML = New-Object -Com 'HTMLFile' $HTML.write([ref]$Retires.Summary) $RetirementSummary = $Html.body.innerText try { $HTML = New-Object -Com 'HTMLFile' $HTML.write([ref]$Retires.Description) $RetirementDescriptionFull = $Html.body.innerText $SplitDescription = $RetirementDescriptionFull.split('Help and support').split('Required action') } catch { $SplitDescription = ' ', ' ' } $tmp = @{ 'Subscription' = [string]$Retires.Subscription; 'Tracking ID' = [string]$Retires.TrackingId; 'Status' = [string]$Retires.Status; 'Last Update Time' = [string]$Retires.LastUpdateTime; 'End Time' = [string]$Retires.Endtime; 'Impacted Service' = [string]$Retires.ImpactedService; 'Title' = [string]$Retires.Title; 'Summary' = [string]$RetirementSummary; 'Required Action' = [string]$SplitDescription[1]; 'Details' = [string]$SplitDescription[0] } $Script:RetirementSheet += $tmp } } $Styles4 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 50 -Range 'A1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 25 -Range 'B1:E1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 30 -Range 'F1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 70 -Range 'G1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 80 -Range 'H1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 90 -Range 'I1:J1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -VerticalAlignment Center -WrapText -Range 'A:J' ) # Configure the array of fields to be used in the Retirement sheet $RetirementWorksheet = New-Object System.Collections.Generic.List[System.Object] $RetirementWorksheet.Add('Subscription') $RetirementWorksheet.Add('Tracking ID') $RetirementWorksheet.Add('Status') $RetirementWorksheet.Add('Last Update Time') $RetirementWorksheet.Add('End Time') $RetirementWorksheet.Add('Impacted Service') $RetirementWorksheet.Add('Title') $RetirementWorksheet.Add('Summary') $RetirementWorksheet.Add('Details') $RetirementWorksheet.Add('Required Action') if (![string]::IsNullOrEmpty($Script:RetirementSheet)) { $Script:RetirementSheet | ForEach-Object { [PSCustomObject]$_ } | Select-Object $RetirementWorksheet | Export-Excel -Path $ExcelFile -WorksheetName 'Retirements' -TableName 'TableRetires' -AutoSize -TableStyle $tableStyle -Style $Styles4 } } function Add-SupportTicket { #################### Creates the Tickets sheet $Script:TicketsSheet = @() foreach ($Ticket in $Script:SupportTickets) { if (![string]::IsNullOrEmpty($Ticket)) { $tmp = @{ 'Ticket ID' = [string]$Ticket.'Ticket ID'; 'Severity' = [string]$Ticket.'Severity'; 'Status' = [string]$Ticket.'Status'; 'Support Plan Type' = [string]$Ticket.'Support Plan Type'; 'Creation Date' = [string]$Ticket.'Creation Date'; 'Modified Date' = [string]$Ticket.'Modified Date'; 'Title' = [string]$Ticket.'Title'; 'Related Resource' = [string]$Ticket.'Related Resource' } $Script:TicketsSheet += $tmp } } $Styles5 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 20 -Range 'A1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 15 -Range 'B1:C1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 35 -Range 'D1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 20 -Range 'E1:F1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 50 -Range 'G1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 120 -Range 'H1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -VerticalAlignment Center -WrapText -Range 'A:G' ) # Configure the array of fields to be used in the Tickets sheet $TicketWorksheet = New-Object System.Collections.Generic.List[System.Object] $TicketWorksheet.Add('Ticket ID') $TicketWorksheet.Add('Severity') $TicketWorksheet.Add('Status') $TicketWorksheet.Add('Support Plan Type') $TicketWorksheet.Add('Creation Date') $TicketWorksheet.Add('Modified Date') $TicketWorksheet.Add('Title') $TicketWorksheet.Add('Related Resource') if (![string]::IsNullOrEmpty($Script:TicketsSheet)) { $Script:TicketsSheet | ForEach-Object { [PSCustomObject]$_ } | Select-Object $TicketWorksheet | Export-Excel -Path $ExcelFile -WorksheetName 'Support Tickets' -TableName 'TableTickets' -AutoSize -TableStyle $tableStyle -Style $Styles5 -NoNumberConversion * } } function Add-ServiceHealth { #################### Creates the Service Health sheet $Script:ServiceHealthSheet = @() foreach ($Alert in $Script:ServiceHealth) { if (![string]::IsNullOrEmpty($Alert)) { $Service = if ($Alert.Services.count -gt 1) { $Alert.Services | ForEach-Object { $_ + ' /' } }else { $Alert.Services } $Service = [string]$Service $Service = if ($Service -like '* /*') { $Service -replace '.$' }else { $Service } $EventT = if ($Alert.EventType.count -gt 1) { $Alert.EventType | ForEach-Object { $_ + ' /' } }else { $Alert.EventType } $EventT = [string]$EventT $EventT = if ($EventT -like '* /*') { $EventT -replace '.$' }else { $EventT } $Region = if ($Alert.Regions.count -gt 1) { $Alert.Regions | ForEach-Object { $_ + ' /' } }else { $Alert.Regions } $Region = [string]$Region $Region = if ($Region -like '* /*') { $Region -replace '.$' }else { $Region } $Action = if ($Alert.ActionGroup.count -gt 1) { $Alert.ActionGroup | ForEach-Object { $_ + ' /' } }else { $Alert.ActionGroup } $Action = [string]$Action $Action = if ($Action -like '* /*') { $Action -replace '.$' }else { $Action } $tmp = @{ 'Name' = [string]$Alert.Name; 'Enabled' = [string]$Alert.Enabled; 'Subscription' = [string]$Alert.Subscription; 'Services' = $Service; 'Event Type' = $EventT; 'Regions' = $Region; 'Action Group' = $Action } $Script:ServiceHealthSheet += $tmp } } $Styles6 = @( New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 25 -NumberFormat '0' -Range 'A1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 15 -NumberFormat '0' -Range 'B1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -FontColor 'White' -VerticalAlignment Center -Bold -WrapText -BackgroundColor 'DarkSlateGray' -Width 35 -NumberFormat '0' -Range 'C1:G1' New-ExcelStyle -HorizontalAlignment Center -FontName 'Calibri' -FontSize 11 -VerticalAlignment Center -WrapText -NumberFormat '0' -Range 'A:G' ) # Configure the array of fields to be used in the Tickets sheet $ServiceHealthtWorksheet = New-Object System.Collections.Generic.List[System.Object] $ServiceHealthtWorksheet.Add('Name') $ServiceHealthtWorksheet.Add('Enabled') $ServiceHealthtWorksheet.Add('Subscription') $ServiceHealthtWorksheet.Add('Services') $ServiceHealthtWorksheet.Add('Event Type') $ServiceHealthtWorksheet.Add('Regions') $ServiceHealthtWorksheet.Add('Action Group') if (![string]::IsNullOrEmpty($Script:ServiceHealthSheet)) { $Script:ServiceHealthSheet | ForEach-Object { [PSCustomObject]$_ } | Select-Object $ServiceHealthtWorksheet | Export-Excel -Path $ExcelFile -WorksheetName 'Health Alerts' -TableName 'TableAlerts' -AutoSize -TableStyle $tableStyle -Style $Styles6 } } function Add-Recommendation { #################### Starts to process the main sheet foreach ($customRec in $Script:CustomYAMLContent) { $countFormula = 'COUNTIFS(ImpactedResources!D:D,"' + $customRec.aprlGuid + '",ImpactedResources!S:S,"' + $customRec.checkName + '")' $compliantFormula = 'IF((' + $countFormula + ')>0,"No","Yes")' $ExcelCat = Set-RecommendationControl -category $customRec.recommendationControl $Script:Recommendations += @{ 'Implemented?Yes/No' = "=$compliantFormula" 'Number of Impacted Resources?' = "=$countFormula" 'Azure Service / Well-Architected' = 'Custom' 'Recommendation Source' = 'Custom' 'Resiliency Category' = $ExcelCat 'Azure Service Category / Well-Architected Area' = 'Custom' 'Azure Service / Well-Architected Topic' = 'Custom' 'Recommendation Title' = [string]$customRec.description 'Impact' = [string]$customRec.recommendationImpact 'Best Practices Guidance' = [string]$customRec.longDescription 'Read More' = [string]$customRec.learnMoreLink.url 'Potential Benefits' = [string]$customRec.potentialBenefits 'Add associated Outage TrackingID and/or Support Request # and/or Service Retirement TrackingID' = '' 'Observation / Annotation' = '' 'Recommendation Id' = $customRec.aprlGuid } } # Build the APRL Recommendations foreach ($Service in $Script:ServicesYAMLContent) { if ($Service.recommendationMetadataState -eq 'Active') { if (($Service.recommendationResourceType -like 'Specialized.Workload/*' -or $Service.recommendationResourceType -eq 'Microsoft.Subscription/Subscriptions' -or $Service.recommendationResourceType -in $Script:AllResourceTypesOrdered.'Resource Type' -or $Script:FilterRecommendations -eq $false) -and ([string]::IsNullOrEmpty($Service.recommendationTypeId) -or (![string]::IsNullOrEmpty($Service.recommendationTypeId) -and $Service.recommendationTypeId -notin $Script:RecommendedAdv))) { $ID = $Service.aprlGuid $resourceType = $Service.recommendationResourceType $ExcelCat = Set-RecommendationControl -category $Service.recommendationControl $tmp = @{ 'Implemented?Yes/No' = ('=IF((COUNTIF(ImpactedResources!D:D,"' + $ID + '")=0),"Yes","No")'); 'Number of Impacted Resources?' = ('=COUNTIF(ImpactedResources!D:D,"' + $ID + '")'); 'Azure Service / Well-Architected' = 'Azure Service'; 'Recommendation Source' = 'APRL'; 'Resiliency Category' = $ExcelCat; 'Azure Service Category / Well-Architected Area' = if ($resourceType -like 'Specialized.Workload/*') { $resourceType }else { ($resourceType.split('/')[0]) }; 'Azure Service / Well-Architected Topic' = if ($resourceType -like 'Specialized.Workload/*') { $resourceType }else { ($resourceType.split('/')[1]) }; 'Recommendation Title' = $Service.description; 'Impact' = $Service.recommendationImpact; 'Best Practices Guidance' = [string]$Service.longDescription; 'Read More' = [string]($Service.learnMoreLink.url -join "`n"); 'Potential Benefits' = [string]$Service.potentialBenefits; 'Add associated Outage TrackingID and/or Support Request # and/or Service Retirement TrackingID' = ''; 'Observation / Annotation' = ''; 'Recommendation Id' = [string]$Service.aprlGuid } $Script:Recommendations += $tmp } } } # Builds the Advisor recommendations foreach ($advisor in $Script:AdvisorContent) { if ($advisor.recommendationId -in $Script:RecommendedAdv) { $ID = $advisor.recommendationId $resourceType = $advisor.type.ToLower() $ExcelCat = Set-RecommendationControl -category $advisor.category $tmp = @{ 'Implemented?Yes/No' = ('=IF((COUNTIF(ImpactedResources!D:D,"' + $ID + '")=0),"Yes","No")'); 'Number of Impacted Resources?' = ('=COUNTIF(ImpactedResources!D:D,"' + $ID + '")'); 'Azure Service / Well-Architected' = 'Azure Service'; 'Recommendation Source' = 'ADVISOR'; 'Resiliency Category' = $ExcelCat; 'Azure Service Category / Well-Architected Area' = ($resourceType.split('/')[0]); 'Azure Service / Well-Architected Topic' = ($resourceType.split('/')[1]); 'Recommendation Title' = $advisor.description; 'Impact' = $advisor.impact; 'Best Practices Guidance' = $advisor.description; 'Read More' = ''; 'Potential Benefits' = ''; 'Add associated Outage TrackingID and/or Support Request # and/or Service Retirement TrackingID' = ''; 'Observation / Annotation' = ''; 'Recommendation Id' = [string]$advisor.recommendationId } $Script:Recommendations += $tmp } } # Builds the WAF recommendations foreach ($WAFYAML in $Script:WAFYAMLContent) { $resourceType = $WAFYAML.recommendationResourceType $ID = $WAFYAML.aprlGuid $ExcelCat = Set-RecommendationControl -category $WAFYAML.recommendationControl $tmp = @{ 'Implemented?Yes/No' = ('=IF((COUNTIF(ImpactedResources!D:D,"' + $ID + '")=0),"Yes","No")'); 'Number of Impacted Resources?' = ('=COUNTIF(ImpactedResources!D:D,"' + $ID + '")'); 'Azure Service / Well-Architected' = 'Well Architected'; 'Recommendation Source' = 'APRL'; 'Resiliency Category' = $ExcelCat; 'Azure Service Category / Well-Architected Area' = ($resourceType.split('/')[0]); 'Azure Service / Well-Architected Topic' = ($resourceType.split('/')[1]); 'Recommendation Title' = $WAFYAML.description; 'Impact' = $WAFYAML.recommendationImpact; 'Best Practices Guidance' = [string]$WAFYAML.longDescription; 'Read More' = [string]($WAFYAML.learnMoreLink.url -join "`n"); 'Potential Benefits' = [string]$WAFYAML.potentialBenefits; 'Add associated Outage TrackingID and/or Support Request # and/or Service Retirement TrackingID' = ''; 'Observation / Annotation' = ''; 'Recommendation Id' = [string]$WAFYAML.aprlGuid } $Script:Recommendations += $tmp } $columnCommonStyle = @{ FontName = 'Calibri' FontSize = 11 WrapText = $true } $headerCommonStyle = @{ FontName = 'Calibri' FontSize = 11 FontColor = 'White' Bold = $true BackgroundColor = 'DarkSlateGray' HorizontalAlignment = 'Center' VerticalAlignment = 'Center' WrapText = $true } $Styles2 = @( # Apply the style to the columns. New-ExcelStyle @columnCommonStyle -Range 'A:G' -HorizontalAlignment Center -VerticalAlignment Center New-ExcelStyle @columnCommonStyle -Range 'H:H' -HorizontalAlignment Left -VerticalAlignment Center New-ExcelStyle @columnCommonStyle -Range 'I:I' -HorizontalAlignment Center -VerticalAlignment Center New-ExcelStyle @columnCommonStyle -Range 'J:K' -HorizontalAlignment Left -VerticalAlignment Top New-ExcelStyle @columnCommonStyle -Range 'L:L' -HorizontalAlignment Center -VerticalAlignment Center New-ExcelStyle @columnCommonStyle -Range 'M:M' -HorizontalAlignment Left -VerticalAlignment Top New-ExcelStyle @columnCommonStyle -Range 'N:N' -HorizontalAlignment Center -VerticalAlignment Center # Apply the style to the header row. New-ExcelStyle @headerCommonStyle -Range 'A1:B1' -Width 14 New-ExcelStyle @headerCommonStyle -Range 'C1' -Width 18 New-ExcelStyle @headerCommonStyle -Range 'D1' -Width 20 New-ExcelStyle @headerCommonStyle -Range 'E1:F1' -Width 35 New-ExcelStyle @headerCommonStyle -Range 'G1' -Width 20 New-ExcelStyle @headerCommonStyle -Range 'H1' -Width 55 New-ExcelStyle @headerCommonStyle -Range 'I1' -Width 10 New-ExcelStyle @headerCommonStyle -Range 'J1' -Width 90 New-ExcelStyle @headerCommonStyle -Range 'K1' -Width 45 New-ExcelStyle @headerCommonStyle -Range 'L1:M1' -Width 35 New-ExcelStyle @headerCommonStyle -Range 'N1' -Width 45 ) # Configure the array of fields to be used in the Recommendations sheet $FinalWorksheet = New-Object System.Collections.Generic.List[System.Object] $FinalWorksheet.Add('Implemented?Yes/No') $FinalWorksheet.Add('Number of Impacted Resources?') $FinalWorksheet.Add('Azure Service / Well-Architected') $FinalWorksheet.Add('Recommendation Source') $FinalWorksheet.Add('Azure Service Category / Well-Architected Area') $FinalWorksheet.Add('Azure Service / Well-Architected Topic') $FinalWorksheet.Add('Resiliency Category') $FinalWorksheet.Add('Recommendation Title') $FinalWorksheet.Add('Impact') $FinalWorksheet.Add('Best Practices Guidance') $FinalWorksheet.Add('Read More') $FinalWorksheet.Add('Add associated Outage TrackingID and/or Support Request # and/or Service Retirement TrackingID') $FinalWorksheet.Add('Observation / Annotation') $FinalWorksheet.Add('Recommendation Id') #################### Creates the recommendations sheet in Excel $Script:Recommendations | ForEach-Object { [PSCustomObject]$_ } | Select-Object $FinalWorksheet | Export-Excel -Path $ExcelFile -WorksheetName 'Recommendations' -TableName 'Table1' -AutoSize -TableStyle $tableStyle -Style $Styles2 -MoveToStart } function Build-PivotTable { #################### Creates the empty PivotTable sheet to be used later '' | Export-Excel -Path $ExcelFile -WorksheetName 'PivotTable' #################### Creates the Charts sheet and already add the first line with the yellow background $StyleOver = New-ExcelStyle -Range A1:G1 -Bold -FontSize 11 -BackgroundColor ([System.Drawing.Color]::Yellow) -Merge -HorizontalAlignment Left 'Copy the Charts below to your Word and Powerpoint Documents' | Export-Excel -Path $ExcelFile -WorksheetName 'Charts' -Style $StyleOver # Open the Excel file to add the Pivot Tables and Charts $Excel = Open-ExcelPackage -Path $ExcelFile $PTParams = @{ PivotTableName = 'P0' Address = $Excel.PivotTable.cells['A3'] SourceWorkSheet = $Excel.Recommendations PivotRows = @('Azure Service / Well-Architected', 'Azure Service / Well-Architected Topic') PivotColumns = @('Impact') PivotData = @{'Azure Service Category / Well-Architected Area' = 'Count' } PivotTableStyle = 'Medium8' Activate = $true PivotFilter = 'Implemented?Yes/No' ShowPercent = $true IncludePivotChart = $true #ShowCategory = $true ChartType = 'BarClustered' ChartRow = 80 ChartColumn = 3 NoLegend = $false ChartTitle = 'Recommendations per Services/Well-Architected Area' ChartHeight = 696 ChartWidth = 450 } Add-PivotTable @PTParams $PTParams = @{ PivotTableName = 'P1' Address = $Excel.PivotTable.cells['H3'] SourceWorkSheet = $Excel.Recommendations PivotRows = @('Resiliency Category') PivotColumns = @('Impact') PivotData = @{'Resiliency Category' = 'Count' } PivotTableStyle = 'Medium9' Activate = $true PivotFilter = 'Implemented?Yes/No' ShowPercent = $true IncludePivotChart = $true ChartType = 'BarClustered' ChartRow = 80 ChartColumn = 30 NoLegend = $false ChartTitle = 'Recommendations per Resiliency Category' ChartHeight = 569 ChartWidth = 462 } Add-PivotTable @PTParams Close-ExcelPackage $Excel } function Invoke-ExcelAPI { Write-Host 'Opening Excel...' $Script:ExcelApplication = New-Object -ComObject Excel.Application Start-Sleep 2 Write-Host 'Customizing Excel Charts. ' # Open the Excel using the API to move the charts from the PivotTable sheet to the Charts sheet and change chart style, font, etc.. if ($Script:ExcelApplication) { try { Write-Debug 'Opening Excel File' $Ex = $ExcelApplication.Workbooks.Open($ExcelFile) Start-Sleep -Seconds 2 Write-Debug 'Opening Excel Sheets' $WS = $ex.Worksheets | Where-Object { $_.Name -eq 'PivotTable' } $WS2 = $ex.Worksheets | Where-Object { $_.Name -eq 'Charts' } Write-Debug 'Moving Charts to Chart sheet' ($WS.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Cut() $WS2.Paste() ($WS.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Cut() $WS2.Paste() Write-Debug 'Reloading Excel Chart Sheet' $WS2 = $ex.Worksheets | Where-Object { $_.Name -eq 'Charts' } Write-Debug 'Editing ChartP0' ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartStyle = 222 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartArea.Font.Name = 'Segoe UI' ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartArea.Font.Size = 9 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartArea.Left = 18 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP0' }).DrawingObject.Chart.ChartArea.Top = 40 Write-Debug 'Editing ChartP1' ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartStyle = 222 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartArea.Font.Name = 'Segoe UI' ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartArea.Font.Size = 9 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartArea.Left = 555 ($WS2.Shapes | Where-Object { $_.name -eq 'ChartP1' }).DrawingObject.Chart.ChartArea.Top = 40 Write-Debug 'Editing Pivot Filters' $WS.Range('B1').Formula = 'No' $WS.Range('I1').Formula = 'No' Write-Debug 'Saving File' $Ex.Save() Write-Debug 'Closing Excel Application' $Ex.Close() $ExcelApplication.Quit() # Ensures the Excel process opened by the API is closed Write-Debug 'Ensuring Excel Process is Closed.' Get-Process -Name 'excel' -ErrorAction Ignore | Where-Object { $_.CommandLine -like '*/automation*' } | Stop-Process } catch { Write-Host 'Error during the PivotTable + Charts customization' -BackgroundColor DarkRed } } } Add-ImpactedResource Add-ResourceType Add-Outage Add-Retirement Add-SupportTicket Add-ServiceHealth Add-Recommendation Build-PivotTable Invoke-ExcelAPI } #Call the functions $Script:Version = '2.1.18' Write-Host 'Version: ' -NoNewline Write-Host $Script:Version -ForegroundColor DarkBlue if ($Help.IsPresent) { Get-HelpMessage Exit } Write-Debug 'Calling Function: Test-Requirement' Test-Requirement Write-Debug 'Calling Function: Set-LocalFile' Set-LocalFile Write-Debug 'Calling Function: Convert-JSON' Convert-JSON Write-Debug 'Calling Function: Build-ExcelFile' Build-ExcelFile } $TotalTime = $Script:Runtime.Totalminutes.ToString('#######.##') Write-Host '---------------------------------------------------------------------' Write-Host ('Execution Complete. Total Runtime was: ') -NoNewline Write-Host $TotalTime -NoNewline -ForegroundColor Cyan Write-Host (' Minutes') Write-Host 'Excel File: ' -NoNewline Write-Host $Script:ExcelFile -ForegroundColor Blue Write-Host '---------------------------------------------------------------------' |