Featured

Turn on Azure Update Management update scheduling

Recently, I've approached the task of automating implementing Azure Updates with Azure Automation.  I tried to do all of this task with Powershell and again with an ARM template(JSON), but neither worked well, by themselves.  Combining them, however, seems to be a good approach.  Copy these code blocks, save the first one as a Powershell file, the second block is JSON, third is an example variable file.  Save them to the same directory as the Powershell file.  Update the Powershell on line 275 with the directory information.  I couldn't get it working without using absolute paths. 


When run, it will be command-line interactive with a few choices.  1.  Lookup information.  2. Create a new LAW and deploy.  3.  Deploy using an existing LAW.  Regardless of these choices, after you finally press 'N' to stop repeating the loop, it will deploy the JSON using values from the Powershell, specifically the LAW name.  Other variable values can be changed this same way.


The script also builds an Azure Query for the Update Schedule.  Check line 229-243 to make changes to the query.

Import-Module Az.OperationalInsights,Az.Automation,Az.Resources

$subId = "XXXX"

# Connect and set context
Write-Host "Connecting to Azure and setting Az Context..." -ForegroundColor Green -BackgroundColor Black
Connect-AzAccount -Subscription $subId
Write-Host "Connected to Subscription: $subId" -ForegroundColor Green -BackgroundColor Black

# 
Do {
    $ResourceGroup = "rrautomation"
    $title = Write-Output "Thank you" 
    $prompt = Write-Output "Please select from these options: "
    $choices =@(
        ("&E - Exit"),
        ("&1 - Check if rrautomation account exists and if not, create it. Output existing Log Analytics Workspace names for Az Updates usage"),
        ("&2 - Create new Log Analytics Workspace and deploy"),
        ("&3 - Enable Az Update on existing LAW")
    )
    
    $choicedesc = New-Object System.Collections.ObjectModel.Collection[System.Management.Automation.Host.ChoiceDescription]
    for($i=0; $i -lt $choices.length; $i++){
        $choicedesc.Add((New-Object System.Management.Automation.Host.ChoiceDescription $choices[$i] ) ) }
    [int]$defchoice = 0
    $action = $host.ui.PromptForChoice($title, $prompt, $choices, $defchoice)
    Switch ($action)
    {
     0 {
            Write-Host "(X) Exited Function." -ForegroundColor Red 
            break
        }
     1 {
        try {
            Write-Host "Checking if RR Automation Resource Group exists..." -ForegroundColor Cyan 
            Get-AzResourceGroup -Name $ResourceGroup -ErrorAction Stop
        } 
        catch {
            Write-Host "(!) Resource Group does not exist, creating..." -ForegroundColor Green 
            New-AzResourceGroup -Name $ResourceGroup -Location $Location
        }
        try {
            Write-Host "Getting rrautomation account..."
            Get-AzAutomationAccount -Name "rrautomation" -ResourceGroupName $ResourceGroup -ErrorAction Stop
        }
        catch {
            Write-Host "(!) Automation account doesn't exist, creating..."
            New-AzAutomationAccount -Name "rrautomation" -ResourceGroupName $ResourceGroup -Location "East US"
        }
        try     {
            Write-Host "Getting Workspaces in Resource Group: $ResourceGroup" -ForegroundColor Green 
            $logNames = Get-AzOperationalInsightsWorkspace -ResourceGroupName $ResourceGroup
            if ($logNames = null) {
                Throw "(X) There are no Workspaces in $ResourceGroup : $_" 
            }
            else {
                $logNames
                Write-Host "-----------------------REPEAT AND SELECT #3-------------------------" -ForegroundColor Green -BackgroundColor Black
            }
        } 
        catch   {
            Write-Host "(X) There are no Log Analytics Workspaces in Resource Group: $ResourceGroup" -ForegroundColor Red 
            Write-Host ""
            Write-Host "Getting ALL Workspaces..." -ForegroundColor Magenta 
            try {
                Get-AzOperationalInsightsWorkspace -ErrorAction Stop
            }
            catch {
                Write-Host "(X) Unable to get other Workspaces..." -ForegroundColor Red 
                break
            }
            Write-Host "-----------------------REPEAT AND SELECT #2-----------------------" -ForegroundColor Green -BackgroundColor Black
        }
        }
     2 {
        $WorkspaceName = "log-analytics-" + (Get-Random -Maximum 99999)
        $Location = "eastus2"
        Write-Host "Creating Log Analytics Workspace..." -ForegroundColor Green -BackgroundColor Black
        $lawName = New-AzOperationalInsightsWorkspace -Location $Location -Name $WorkspaceName -Sku PerGB2018 -ResourceGroupName $ResourceGroup
        Write-Host "Log Analytics Workspace created successfully..."
        $workspaceKey = (Get-AzOperationalInsightsWorkspaceSharedKeys -ResourceGroupName $lawName.ResourceGroupName -Name $lawName.Name).PrimarySharedKey
        $workspaceId = $lawName.CustomerID
        $runningList = [System.Collections.ArrayList]::new()
        Write-Host "Connecting VMs to LAW..." -ForegroundColor Cyan
        $vmNames = Get-AzVM -status
        foreach ($vm in $vmNames) {
        if ($vm.PowerState -eq "VM running")
        {
            [void]$runningList.Add([PSCustomObject]@{
                VM_Name = $vm.Name
                VM_ResourceGroupName = $vm.ResourceGroupName
                VM_Location = $vm.Location
                })
        }
        else {
            Write-Host "$($vm.Name) is not Powered on.  Cannot set VM Extension unless VM is running." -ForegroundColor Yellow
        }
    }
        Write-Host "Running VMs:" -ForegroundColor Cyan
        $runningList | Out-Default
        foreach ($vm in $runningList) {
            try {
                Set-AzVMExtension -ResourceGroupName $vm.VM_ResourceGroupName -VMName $vm.VM_Name -Name 'MicrosoftMonitoringAgent' -Publisher 'Microsoft.EnterpriseCloud.Monitoring' -ExtensionType 'MicrosoftMonitoringAgent' -Location $vm.VM_Location -SettingString "{'workspaceId': '$workspaceId'}" -ProtectedSettingString "{'workspaceKey': '$workspaceKey'}" -ErrorAction Stop | Out-Null
                Write-Host "Connected VM($($vm.VM_Name)) to LAW: $($lawName.Name)" -ForegroundColor Green
            }
            catch {
                Write-Host "(X) Unable to set VM($($vm.VM_Name)) link to Log Analytics Workspace.  Related Error:" -ForegroundColor Red
                Write-Host ""
                Write-Host "$_" -ForegroundColor Red
            }
    }
        $ScheduleTime = ((Get-Date).AddMinutes(15))
        $AutomationAccountName = "rrautomation"
        Write-Host "Creating Update Deployment Schedules..." -ForegroundColor Green
        $automationScheduleName = "Group 1 Deploy Updates"
        # Create the parameters for setting the schedule
        $ScheduleParameters = @{
            ResourceGroupName     = $ResourceGroup
            AutomationAccountName = $AutomationAccountName
            name                  = $automationScheduleName
            StartTime             = $ScheduleTime
            ExpiryTime            = ((Get-Date).AddYears(5))
            MonthInterval         = 1
            DayOfWeekOccurrence   = 3
            DayOfWeek             = 4
        }
        # Create the schedule that will be used for the updates
        Try {
            $AutomationSchedule = New-AzAutomationSchedule @ScheduleParameters -ErrorAction Stop -Verbose 
            Write-Host "Schedule has been created!" -ForegroundColor Green
        }
        Catch {
            Throw "(X) Could not create Automation Schedule: $_"
        }
        # Query parameters
        [array]$Scope = @("/subscriptions/$((Get-AzContext).Subscription.Id)")
        $QueryParameters = @{
            ResourceGroupName     = $ResourceGroup
            AutomationAccountName = $AutomationAccountName
            Scope                 = $Scope
            Location              = $Location
            Tag                   = @{"AzUpdates" = "Group 1" }
        }
        Try {
            $AzQuery = New-AzAutomationUpdateManagementAzureQuery @QueryParameters -Verbose -ErrorAction Stop
            Write-Host "Query has been created!" -ForegroundColor Green
        }
        Catch {
            Throw "(X) Could not create query: $_"
        }
        # Link schedule to Patching runbook
        $UpdateParameters = @{
            ResourceGroupName            = $ResourceGroup
            AutomationAccountName        = $AutomationAccountName
            Schedule                     = $AutomationSchedule
            Windows                      = $true
            Duration                     = New-TimeSpan -Hours 3
            RebootSetting                = "Always"
            IncludedUpdateClassification = "Security"
            AzureQuery                   = $AzQuery
        }
        Try {
            New-AzAutomationSoftwareUpdateConfiguration @UpdateParameters -Verbose -ErrorAction Stop
        }
        Catch {
            Throw "(X) Could not create update schedule: $_"
        }
        Write-Host "-----------------------Azure Updates Setup Complete!-----------------------" -ForegroundColor Green -BackgroundColor Black
        }
     3 {
        $lawName = Read-Host -Prompt "Please enter existing Log Analytics Workspace Name: "
        $logName = Get-AzOperationalInsightsWorkspace -Name $lawName -ResourceGroupName $ResourceGroup
        $workspaceKey = (Get-AzOperationalInsightsWorkspaceSharedKeys -ResourceGroupName $logName.ResourceGroupName -Name $logName.Name).PrimarySharedKey
        $workspaceId = $logName.CustomerID
        $runningList = [System.Collections.ArrayList]::new()
        Write-Host "Connecting VMs to LAW..." -ForegroundColor Cyan
        $vmNames = Get-AzVM -status
        foreach ($vm in $vmNames) {
        if ($vm.PowerState -eq "VM running")
        {
            [void]$runningList.Add([PSCustomObject]@{
                VM_Name = $vm.Name
                VM_ResourceGroupName = $vm.ResourceGroupName
                VM_Location = $vm.Location
                })
        }
        else {
            Write-Host "(!) $($vm.Name) is not Powered on.  Cannot set VM Extension unless VM is running." -ForegroundColor Yellow
        }
    }
        Write-Host ""
        Write-Host "Running VMs:" -ForegroundColor Cyan
        $runningList | Out-Default
        foreach ($vm in $runningList) {
            try {
                Set-AzVMExtension -ResourceGroupName $vm.VM_ResourceGroupName -VMName $vm.VM_Name -Name 'MicrosoftMonitoringAgent' -Publisher 'Microsoft.EnterpriseCloud.Monitoring' -ExtensionType 'MicrosoftMonitoringAgent' -Location $vm.VM_Location -SettingString "{'workspaceId': '$workspaceId'}" -ProtectedSettingString "{'workspaceKey': '$workspaceKey'}" -ErrorAction Stop | Out-Null
                Write-Host "Connected VM($($vm.VM_Name)) to LAW: $($lawName.Name)" -ForegroundColor Green
            }
            catch {
                Write-Host "(X) Unable to set VM($($vm.VM_Name)) link to Log Analytics Workspace.  Related Error:" -ForegroundColor Red
                Write-Host ""
                Write-Host "$_" -ForegroundColor Red
            }        
        }
        $ScheduleTime = ((Get-Date).AddMinutes(15))
        $AutomationAccountName = "rrautomation"
        Write-Host "Creating Update Deployment Schedules..." -ForegroundColor Green
        $automationScheduleName = "Group 1 Deploy Updates"
        # Create the parameters for setting the schedule
        $ScheduleParameters = @{
            ResourceGroupName     = $ResourceGroup
            AutomationAccountName = $AutomationAccountName
            name                  = $automationScheduleName
            StartTime             = $ScheduleTime
            ExpiryTime            = ((Get-Date).AddYears(5))
            MonthInterval         = 1
            DayOfWeekOccurrence   = 3
            DayOfWeek             = 4
        }
        # Create the schedule that will be used for the updates
        Try {
            $AutomationSchedule = New-AzAutomationSchedule @ScheduleParameters -ErrorAction Stop -Verbose 
            Write-Verbose "Schedule has been created!"
        }
        Catch {
            Throw "(X) Could not create Automation Schedule: $_"
        }
        # Query parameters
        [array]$Scope = @("/subscriptions/$((Get-AzContext).Subscription.Id)")
        $QueryParameters = @{
            ResourceGroupName     = $ResourceGroup
            AutomationAccountName = $AutomationAccountName
            Scope                 = $Scope
            Location              = $Location
            Tag                   = @{"AzUpdates" = "Group 1" }
        }
        Try {
            $AzQuery = New-AzAutomationUpdateManagementAzureQuery @QueryParameters -Verbose -ErrorAction Stop
            Write-Host "Query has been created!" -ForegroundColor Green
        }
        Catch {
            Throw "(X) Could not create query: $_"
        }
        # Link schedule to Patching runbook
        $UpdateParameters = @{
            ResourceGroupName            = $ResourceGroup
            AutomationAccountName        = $AutomationAccountName
            Schedule                     = $AutomationSchedule
            Windows                      = $true
            Duration                     = New-TimeSpan -Hours 3
            RebootSetting                = "Always"
            IncludedUpdateClassification = "Security"
            AzureQuery                   = $AzQuery
        }
        Try {
            New-AzAutomationSoftwareUpdateConfiguration @UpdateParameters -Verbose -ErrorAction Stop
        }
        Catch {
            Throw "(X) Could not create update schedule: $_"
        }
        Write-Host "-----------------------Azure Updates Setup Complete!-----------------------" -ForegroundColor White -BackgroundColor Blue
    }
}
    $repeat = Read-Host "Repeat?"
    }
    While ($repeat -eq "Y")
    Write-Host "Updating parameters.json file with log analytics workspace name..." -ForegroundColor Cyan
    $json = Get-Content "parameters.json" | ConvertFrom-Json
    $json.parameters.workspaceName.value = "$($lawName)"
    $json | ConvertTo-Json | Out-File "parameters.json"
    Write-Host ""
    Write-Host "parameters.json file updated!..." -ForegroundColor Green
    Write-Host "Deploying Azure Updates Dashboard Workbook, Alerts, and linking automation account to LAW..." -ForegroundColor Cyan
    Connect-AzAccount 
    New-AzResourceGroupDeployment -Name "Azure-Automation-Update-Management" -ResourceGroupName $ResourceGroup -TemplateUri "\workbook.json" -TemplateParameterFile "\parameters.json"
    Write-Host ""
    Write-Host "EXITING... " -ForegroundColor Yellow -BackgroundColor Black
    Write-Host ""
    Disconnect-AzAccount > $null
    Write-Host "-----------------------ACCOUNT HAS BEEN DISCONNECTED-----------------------" -ForegroundColor Red -BackgroundColor Black
    #end
{
    "contentVersion": "1.0.0.0",
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "parameters": {
      "workbookDisplayName": {
        "type": "string",
        "defaultValue": "Windows Update Summary",
        "metadata": {
          "description": "The friendly name for the workbook that is used in the Gallery or Saved List.  This name must be unique within a resource group."
        }
      },
      "workbookType": {
        "type": "string",
        "defaultValue": "workbook",
        "metadata": {
          "description": "The gallery that the workbook will been shown under. Supported values include workbook, tsg, etc. Usually, this is 'workbook'"
        }
      },
      "workbookSourceId": {
        "type": "string",
        "defaultValue": "Azure Monitor",
        "metadata": {
          "description": "The id of resource instance to which the workbook will be associated"
        }
      },
      "workbookId": {
        "type": "string",
        "defaultValue": "[newGuid()]",
        "metadata": {
          "description": "The unique guid for this workbook instance"
        }
      },
      "actionGroupName": {
        "type": "string",
        "metadata": {
          "description": "Unique name (within the Resource Group) for the Action group."
        }
      },
      "actionGroupShortName": {
        "defaultValue": "RR Email",
        "type": "string",
        "metadata": {
          "description": "Short name (maximum 12 characters) for the Action group."
        }
      },
      "alertEmailAddress": {
        "type": "string",
        "metadata": {
          "description": "Should be name@domain.com"
        }
      },
      "client_Name": {
        "type": "string"
      },
      "subscription_Id": {
        "type": "string"
      },
      "location": {
        "type": "string",
        "defaultValue": "[resourceGroup().location]",
        "metadata": {
          "description": "Specifies the location in which to create the workspace."
        }
       },
       "workspaceName": {
        "type": "string",
        "metadata": {
          "description": "Specifies the name of the Workspace."
        }
        },
        "automationAccountName": {
            "type": "string",
            "metadata": {
              "description": "Specifies the name of the Workspace."
            }
        },
        "resourceGroup": {
          "type": "string",
          "metadata": {
            "description": "Specifies the name of the Resource Group."
          }
      }
    },
    "resources": [
      {
        "name": "[parameters('workbookId')]",
        "type": "microsoft.insights/workbooks",
        "location": "[resourceGroup().location]",
        "apiVersion": "2018-06-17-preview",
        "dependsOn": [],
        "kind": "shared",
        "properties": {
          "displayName": "[parameters('workbookDisplayName')]",
          "serializedData": "{\"version\":\"Notebook/1.0\",\"items\":[{\"type\":1,\"content\":\"{\\\"json\\\":\\\"\\\"}\",\"customWidth\":\"100\",\"name\":\"text - 5\"},{\"type\":9,\"content\":\"{\\\"version\\\":\\\"KqlParameterItem/1.0\\\",\\\"crossComponentResources\\\":[\\\"value::all\\\"],\\\"parameters\\\":[{\\\"id\\\":\\\"f8d6705a-e284-4077-8113-aae1038a6b7c\\\",\\\"version\\\":\\\"KqlParameterItem/1.0\\\",\\\"name\\\":\\\"Workspaces\\\",\\\"type\\\":5,\\\"isRequired\\\":true,\\\"multiSelect\\\":true,\\\"quote\\\":\\\"'\\\",\\\"delimiter\\\":\\\",\\\",\\\"query\\\":\\\"where type =~ 'microsoft.operationalinsights/workspaces'\\\\r\\\\n| summarize by id, name\\\",\\\"crossComponentResources\\\":[\\\"value::all\\\"],\\\"value\\\":[\\\"value::all\\\"],\\\"typeSettings\\\":{\\\"additionalResourceOptions\\\":[\\\"value::1\\\",\\\"value::all\\\"]},\\\"queryType\\\":1,\\\"resourceType\\\":\\\"microsoft.resourcegraph/resources\\\"}],\\\"style\\\":\\\"pills\\\",\\\"queryType\\\":1,\\\"resourceType\\\":\\\"microsoft.resourcegraph/resources\\\"}\",\"name\":\"parameters - 11\"},{\"type\":1,\"content\":\"{\\\"json\\\":\\\"# Azure Automation Windows Update Summary for All Subscriptions\\\\r\\\\n\\\\r\\\\nThis workbook can query multiple Log Analytics Workspaces. The Azure Automation Update Management solution needs to be linked to every Log Analytics Workspaces you wish to use it with.\\\"}\",\"name\":\"text - 6\"},{\"type\":1,\"content\":\"{\\\"json\\\":\\\"## Windows Updates need by Classification\\\"}\",\"customWidth\":\"50\",\"name\":\"text - 7\"},{\"type\":1,\"content\":\"{\\\"json\\\":\\\"## Top 5 Windows Machines by Update Count\\\"}\",\"customWidth\":\"50\",\"name\":\"text - 8\"},{\"type\":3,\"content\":\"{\\\"version\\\":\\\"KqlItem/1.0\\\",\\\"query\\\":\\\"Update\\\\r\\\\n| where TimeGenerated>ago(14h) and OSType!=\\\\\\\"Linux\\\\\\\" and (Optional==false or Classification has \\\\\\\"Critical\\\\\\\" or Classification has \\\\\\\"Security\\\\\\\") and SourceComputerId in ((Heartbeat\\\\r\\\\n| where TimeGenerated>ago(12h) and OSType=~\\\\\\\"Windows\\\\\\\" and notempty(Computer)\\\\r\\\\n| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\\\\r\\\\n| where Solutions has \\\\\\\"updates\\\\\\\" | distinct SourceComputerId))\\\\r\\\\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer, SourceComputerId, UpdateID\\\\r\\\\n| where UpdateState=~\\\\\\\"Needed\\\\\\\" and Approved!=false\\\\r\\\\n| summarize UpdatesNeeded=count(Classification) by Classification\\\",\\\"size\\\":2,\\\"queryType\\\":0,\\\"resourceType\\\":\\\"microsoft.operationalinsights/workspaces\\\",\\\"crossComponentResources\\\":[\\\"{Workspaces}\\\"],\\\"visualization\\\":\\\"piechart\\\",\\\"tileSettings\\\":{\\\"showBorder\\\":false,\\\"titleContent\\\":{\\\"columnMatch\\\":\\\"Classification\\\",\\\"formatter\\\":1},\\\"leftContent\\\":{\\\"columnMatch\\\":\\\"UpdatesNeeded\\\",\\\"formatter\\\":12,\\\"formatOptions\\\":{\\\"palette\\\":\\\"auto\\\"},\\\"numberFormat\\\":{\\\"unit\\\":17,\\\"options\\\":{\\\"maximumSignificantDigits\\\":3,\\\"maximumFractionDigits\\\":2}}}},\\\"chartSettings\\\":{\\\"seriesLabelSettings\\\":[{\\\"seriesName\\\":\\\"Definition Updates\\\",\\\"color\\\":\\\"yellow\\\"},{\\\"seriesName\\\":\\\"Updates\\\",\\\"color\\\":\\\"orange\\\"},{\\\"seriesName\\\":\\\"Security Updates\\\",\\\"color\\\":\\\"redBright\\\"},{\\\"seriesName\\\":\\\"Update Rollups\\\",\\\"color\\\":\\\"purple\\\"},{\\\"seriesName\\\":\\\"Critical Updates\\\",\\\"color\\\":\\\"red\\\"}]}}\",\"customWidth\":\"50\",\"name\":\"query - 0\"},{\"type\":3,\"content\":\"{\\\"version\\\":\\\"KqlItem/1.0\\\",\\\"query\\\":\\\"Update\\\\r\\\\n| where TimeGenerated>ago(14h) and OSType!=\\\\\\\"Linux\\\\\\\" and (Optional==false or Classification has \\\\\\\"Critical\\\\\\\" or Classification has \\\\\\\"Security\\\\\\\") and SourceComputerId in ((Heartbeat\\\\r\\\\n| where TimeGenerated>ago(12h) and OSType=~\\\\\\\"Windows\\\\\\\" and notempty(Computer)\\\\r\\\\n| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\\\\r\\\\n| where Solutions has \\\\\\\"updates\\\\\\\" | distinct SourceComputerId))\\\\r\\\\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer, SourceComputerId, UpdateID\\\\r\\\\n| where UpdateState=~\\\\\\\"Needed\\\\\\\" and Approved!=false\\\\r\\\\n| project Computer, Title, Classification, PublishedDate, UpdateState, Product\\\\r\\\\n| summarize count(Classification) by Computer \\\\r\\\\n| top 5 by count_Classification desc \\\",\\\"size\\\":2,\\\"queryType\\\":0,\\\"resourceType\\\":\\\"microsoft.operationalinsights/workspaces\\\",\\\"crossComponentResources\\\":[\\\"{Workspaces}\\\"],\\\"visualization\\\":\\\"piechart\\\"}\",\"customWidth\":\"50\",\"name\":\"top five Computers Needing Updates\"},{\"type\":1,\"content\":\"{\\\"json\\\":\\\"## Heatmap of Update Summary by Computer\\\\r\\\\n\\\\r\\\\nThis section is dynamic, by selecting a row that row's Computer name will be exported to populate Updates needed by Server\\\"}\",\"name\":\"text - 9\"},{\"type\":3,\"content\":\"{\\\"version\\\":\\\"KqlItem/1.0\\\",\\\"query\\\":\\\"Heartbeat\\\\r\\\\n| where TimeGenerated>ago(12h) and OSType=~\\\\\\\"Windows\\\\\\\" and notempty(Computer)\\\\r\\\\n| summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId\\\\r\\\\n| where Solutions has \\\\\\\"updates\\\\\\\"\\\\r\\\\n| extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=2, environment=iff(ComputerEnvironment=~\\\\\\\"Azure\\\\\\\", 1, 2), scopedToUpdatesSolution=true, lastUpdateAgentSeenTime=\\\\\\\"\\\\\\\"\\\\r\\\\n| join kind=leftouter\\\\r\\\\n(\\\\r\\\\n    Update\\\\r\\\\n    | where TimeGenerated>ago(14h) and OSType!=\\\\\\\"Linux\\\\\\\" and SourceComputerId in ((Heartbeat\\\\r\\\\n    | where TimeGenerated>ago(12h) and OSType=~\\\\\\\"Windows\\\\\\\" and notempty(Computer)\\\\r\\\\n    | summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\\\\r\\\\n    | where Solutions has \\\\\\\"updates\\\\\\\"\\\\r\\\\n    | distinct SourceComputerId))\\\\r\\\\n    | summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Title, Optional, Approved, Computer, ComputerEnvironment) by Computer, SourceComputerId, UpdateID\\\\r\\\\n    | summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has \\\\\\\"Critical\\\\\\\" and UpdateState=~\\\\\\\"Needed\\\\\\\" and Approved!=false), missingSecurityUpdatesCount=countif(Classification has \\\\\\\"Security\\\\\\\" and UpdateState=~\\\\\\\"Needed\\\\\\\" and Approved!=false), missingOtherUpdatesCount=countif(Classification !has \\\\\\\"Critical\\\\\\\" and Classification !has \\\\\\\"Security\\\\\\\" and UpdateState=~\\\\\\\"Needed\\\\\\\" and Optional==false and Approved!=false), lastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime=\\\\\\\"\\\\\\\" by SourceComputerId\\\\r\\\\n    | extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)\\\\r\\\\n    | extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)\\\\r\\\\n)\\\\r\\\\non SourceComputerId\\\\r\\\\n| project id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, scopedToUpdatesSolution=true, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=2, environment=iff(ComputerEnvironment=~\\\\\\\"Azure\\\\\\\", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2) \\\\r\\\\n| order by ComplianceOrder asc, missingCriticalUpdatesCount desc, missingSecurityUpdatesCount desc, missingOtherUpdatesCount desc, displayName asc\\\\r\\\\n| project displayName, scopedToUpdatesSolution, CriticalUpdates=missingCriticalUpdatesCount, SecurityUpdates=missingSecurityUpdatesCount, OtherUpdates=missingOtherUpdatesCount, compliance, osType, Environment=environment, lastAssessedTime, lastUpdateAgentSeenTime\\\\r\\\\n| extend osType = replace(@\\\\\\\"2\\\\\\\", @\\\\\\\"Windows\\\\\\\", tostring(osType))\\\\r\\\\n| extend osType = replace(@\\\\\\\"1\\\\\\\", @\\\\\\\"Linux\\\\\\\", tostring(osType))\\\\r\\\\n| extend Environment = replace(@\\\\\\\"2\\\\\\\", @\\\\\\\"Non-Azure\\\\\\\", tostring(Environment))\\\\r\\\\n| extend Environment = replace(@\\\\\\\"1\\\\\\\", @\\\\\\\"Azure\\\\\\\", tostring(Environment))\\\",\\\"size\\\":0,\\\"exportFieldName\\\":\\\"displayName\\\",\\\"exportParameterName\\\":\\\"Computer\\\",\\\"queryType\\\":0,\\\"resourceType\\\":\\\"microsoft.operationalinsights/workspaces\\\",\\\"crossComponentResources\\\":[\\\"{Workspaces}\\\"],\\\"visualization\\\":\\\"table\\\",\\\"gridSettings\\\":{\\\"formatters\\\":[{\\\"columnMatch\\\":\\\"displayName\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"scopedToUpdatesSolution\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"CriticalUpdates\\\",\\\"formatter\\\":8,\\\"formatOptions\\\":{\\\"min\\\":0,\\\"max\\\":1,\\\"palette\\\":\\\"greenRed\\\",\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"SecurityUpdates\\\",\\\"formatter\\\":8,\\\"formatOptions\\\":{\\\"min\\\":0,\\\"max\\\":5,\\\"palette\\\":\\\"greenRed\\\",\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"OtherUpdates\\\",\\\"formatter\\\":8,\\\"formatOptions\\\":{\\\"min\\\":0,\\\"max\\\":5,\\\"palette\\\":\\\"greenRed\\\",\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"compliance\\\",\\\"formatter\\\":8,\\\"formatOptions\\\":{\\\"min\\\":1,\\\"max\\\":2,\\\"palette\\\":\\\"greenRed\\\",\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"osType\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"Environment\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"lastAssessedTime\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"lastUpdateAgentSeenTime\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}}]}}\",\"name\":\"query - 2\"},{\"type\":1,\"content\":\"{\\\"json\\\":\\\"## Updates Needed by Server\\\"}\",\"name\":\"text - 10\"},{\"type\":3,\"content\":\"{\\\"version\\\":\\\"KqlItem/1.0\\\",\\\"query\\\":\\\"Update\\\\r\\\\n| where TimeGenerated>ago(14h) and OSType!=\\\\\\\"Linux\\\\\\\" and (Optional==false or Classification has \\\\\\\"Critical\\\\\\\" or Classification has \\\\\\\"Security\\\\\\\") and SourceComputerId in ((Heartbeat\\\\r\\\\n| where TimeGenerated>ago(12h) and OSType=~\\\\\\\"Windows\\\\\\\" and notempty(Computer)\\\\r\\\\n| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId\\\\r\\\\n| where Solutions has \\\\\\\"updates\\\\\\\" | distinct SourceComputerId))\\\\r\\\\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer, SourceComputerId, UpdateID\\\\r\\\\n| where UpdateState=~\\\\\\\"Needed\\\\\\\" and Approved!=false and Computer=='{Computer}'\\\\r\\\\n| project Computer, Title, Classification, PublishedDate, UpdateState, Product\\\",\\\"size\\\":0,\\\"queryType\\\":0,\\\"resourceType\\\":\\\"microsoft.operationalinsights/workspaces\\\",\\\"crossComponentResources\\\":[\\\"{Workspaces}\\\"],\\\"gridSettings\\\":{\\\"formatters\\\":[{\\\"columnMatch\\\":\\\"Computer\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"Title\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"Classification\\\",\\\"formatter\\\":18,\\\"formatOptions\\\":{\\\"showIcon\\\":true,\\\"thresholdsOptions\\\":\\\"colors\\\",\\\"thresholdsGrid\\\":[{\\\"operator\\\":\\\"==\\\",\\\"thresholdValue\\\":\\\"Updates\\\",\\\"representation\\\":\\\"orange\\\",\\\"text\\\":\\\"{0}{1}\\\"},{\\\"operator\\\":\\\"==\\\",\\\"thresholdValue\\\":\\\"Security Updates\\\",\\\"representation\\\":\\\"redBright\\\",\\\"text\\\":\\\"{0}{1}\\\"},{\\\"operator\\\":\\\"==\\\",\\\"thresholdValue\\\":\\\"Definition Updates\\\",\\\"representation\\\":\\\"yellow\\\",\\\"text\\\":\\\"{0}{1}\\\"},{\\\"operator\\\":\\\"==\\\",\\\"thresholdValue\\\":\\\"Update Rollups\\\",\\\"representation\\\":\\\"purple\\\",\\\"text\\\":\\\"{0}{1}\\\"},{\\\"operator\\\":\\\"==\\\",\\\"thresholdValue\\\":\\\"Critical Updates\\\",\\\"representation\\\":\\\"red\\\",\\\"text\\\":\\\"{0}{1}\\\"},{\\\"operator\\\":\\\"Default\\\",\\\"thresholdValue\\\":null,\\\"representation\\\":\\\"blue\\\",\\\"text\\\":\\\"{0}{1}\\\"}]}},{\\\"columnMatch\\\":\\\"PublishedDate\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"UpdateState\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}},{\\\"columnMatch\\\":\\\"Product\\\",\\\"formatter\\\":0,\\\"formatOptions\\\":{\\\"showIcon\\\":true}}]}}\",\"name\":\"query - 4\"}],\"isLocked\":false}",
          "version": "1.0",
          "sourceId": "[parameters('workbookSourceId')]",
          "category": "[parameters('workbookType')]"
        }
      },
      {
        "type": "Microsoft.Insights/actionGroups",
        "apiVersion": "2019-03-01",
        "name": "[parameters('actionGroupName')]",
        "location": "Global",
        "properties": {
          "groupShortName": "[parameters('actionGroupShortName')]",
          "enabled": true,
          "emailReceivers": [
            {
              "name": "Email RR Desk",
              "emailAddress": "[parameters('alertEmailAddress')]",
              "useCommonAlertSchema": true
            }
          ]
        }
      },
      {
        "name": "[concat(parameters('client_Name'), ' - Update Non-Machine Cycle completed')]",
        "type": "Microsoft.Insights/metricAlerts",
        "apiVersion": "2018-03-01",
        "location": "global",
        "tags": {},
        "properties": {
            "description": "",
            "severity": 3,
            "enabled": true,
            "scopes": [
                "[concat(parameters('subscription_Id'),'/resourceGroups/',parameters('resourceGroup'),'/providers/Microsoft.Automation/automationAccounts/', parameters('automationAccountName'))]"
            ],
            "evaluationFrequency": "PT30M",
            "windowSize": "PT30M",
            "criteria": {
                "allOf": [
                    {
                        "threshold": 0,
                        "name": "Metric1",
                        "metricNamespace": "Microsoft.Automation/automationAccounts",
                        "metricName": "TotalUpdateDeploymentRuns",
                        "operator": "GreaterThan",
                        "timeAggregation": "Total",
                        "criterionType": "StaticThresholdCriterion"
                    }
                ],
                "odata.type": "Microsoft.Azure.Monitor.SingleResourceMultipleMetricCriteria"
            },
            "autoMitigate": true,
            "targetResourceType": "Microsoft.Automation/automationAccounts",
            "actions": [
                {
                    "actionGroupId": "[concat(parameters('subscription_Id'),'/resourceGroups/',parameters('resourceGroup'),'/providers/microsoft.insights/actiongroups/',parameters('actionGroupName'))]",
                    "webHookProperties": {}
                }
            ]
        }
    },
    {
        "name": "[concat(parameters('client_Name'), ' - Update cycle completed')]",
        "type": "Microsoft.Insights/metricAlerts",
        "apiVersion": "2018-03-01",
        "location": "global",
        "tags": {},
        "properties": {
            "description": "",
            "severity": 3,
            "enabled": true,
            "scopes": [
                "[concat(parameters('subscription_Id'),'/resourceGroups/',parameters('resourceGroup'),'/providers/Microsoft.Automation/automationAccounts/', parameters('automationAccountName'))]"
            ],
            "evaluationFrequency": "PT30M",
            "windowSize": "PT30M",
            "criteria": {
                "allOf": [
                    {
                        "threshold": 0,
                        "name": "Metric1",
                        "metricNamespace": "Microsoft.Automation/automationAccounts",
                        "metricName": "TotalUpdateDeploymentMachineRuns",
                        "operator": "GreaterThan",
                        "timeAggregation": "Total",
                        "criterionType": "StaticThresholdCriterion"
                    }
                ],
                "odata.type": "Microsoft.Azure.Monitor.SingleResourceMultipleMetricCriteria"
            },
            "autoMitigate": true,
            "targetResourceType": "Microsoft.Automation/automationAccounts",
            "actions": [
                {
                    "actionGroupId": "[concat(parameters('subscription_Id'),'/resourceGroups/',parameters('resourceGroup'),'/providers/microsoft.insights/actiongroups/',parameters('actionGroupName'))]",
                    "webHookProperties": {}
                }
            ]
        }
    },
    {
        "type": "Microsoft.OperationalInsights/workspaces/linkedServices",
        "apiVersion": "2015-11-01-preview",
        "name": "[concat(parameters('workspaceName'), '/' , 'Automation')]",
        "location": "[parameters('location')]",
        "properties": {
          "resourceId": "[resourceId('Microsoft.Automation/automationAccounts', parameters('automationAccountName'))]"
        }
      }
    ]
}
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "actionGroupName": {
      "value": "Email RR Desk"
    },
    "actionGroupShortName": {
      "value": "Email Address"
    },
    "alertEmailAddress": {
      "value": "name@domain.com"
    },
    "client_Name": {
      "value": "Client Name"
    },
    "subscription_Id": {
      "value": "/subscriptions/XXXXXX"
    },
    "workspaceName": {
      "value": "log-analytics-1234"
    },
    "automationAccountName": {
      "value": "rrautomation"
    },
    "resourceGroup": {
      "value": "rrautomation"
    }
  }
}
Featured

Import Powershell object data into SQL

Along the journey with Powershell, you've undoubtedly had a few issues manipulating data, all of which was loaded in memory and in the current session.  But, how can we store the output so we can visualize it?  Easy, stuff it into SQL.

#SQL Server Information 
$SQL_Server = Read-Host "SQL Server?"
$SQL_Database = Read-Host "SQL Database?"
$SQL_Table = Read-Host "SQL Table?"

#Get Count of All Sessions
Connect-AzAccount
$total = 0
$allPools = get-azwvdhostpool -resourcegroupname cloud-azure-na-avd-shared-rg | select Name
foreach ($aPool in $allPools) {
try {
$count = (Get-AzWvdSessionHost -ResourceGroupName cloud-azure-na-avd-shared-rg -HostPoolName $aPool.Name).count
write-host $aPool.Name, $count
$total += $count

#Insert data
$insert_data = "INSERT INTO AVD_Session_Counts ([ColumnName1], [ColumnName2], [ColumnName3]) VALUES ('$(Get-Date -Format 'yyyy/MM/dd HH:mm')','$($aPool.Name)','$([int]$count)');"
Invoke-Sqlcmd -ServerInstance $SQL_Server -Database $SQL_Database -Query $insert_data
}
catch {
Write-host "Whoops"

throw
}
}
#$list > $null
Write-host "Total Count: $total"
Featured

Discover if VM’s have backups configured

Okay, so you can go to each VM blade if you want, or you can cross reference from what already exists in the Recovery Services Vault, but what if you have multiple tenants? It can get ugly, fast. So, here, let me FTFY with a script. It spins through all VMs, discovers if there's a backup configured, it'll grab the info about last backup, and add that to the report. If it doesn't have a backup, you'll have the option to configure them for all of those VMs missing a backup, or a single one, depending on your choice.

param
( 
    [parameter(Mandatory=$true)]
    [string] $subscriptionId
)
Connect-AzAccount
# Set Azure context
$context = Set-AzContext -SubscriptionId $subscriptionId
#Collecting Azure virtual machines Information
Write-Host "Collecting Azure virtual machine Information" -BackgroundColor DarkBlue
$vms = Get-AzVM
#Collecting All Azure backup recovery vaults Information
Write-Host "Collecting all Backup Recovery Vault information" -BackgroundColor DarkBlue
$backupVaults = Get-AzRecoveryServicesVault
$list = [System.Collections.ArrayList]::new()
$vmBackupReport = [System.Collections.ArrayList]::new()
    foreach ($vm in $vms) 
        {
            $recoveryVaultInfo = Get-AzRecoveryServicesBackupStatus -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Type 'AzureVM'
            if ($recoveryVaultInfo.BackedUp -eq $true)
                {
                    Write-Host "$($vm.Name) - BackedUp : Yes" -BackgroundColor DarkGreen
                    #Backup Recovery Vault Information
                    $vmBackupVault = $backupVaults | Where-Object {$_.ID -eq $recoveryVaultInfo.VaultId}
                    #Backup recovery Vault policy Information
                    $container = Get-AzRecoveryServicesBackupContainer -ContainerType AzureVM -VaultId $vmBackupVault.ID -FriendlyName $vm.Name
                    $backupItem = Get-AzRecoveryServicesBackupItem -Container $container -WorkloadType AzureVM -VaultId $vmBackupVault.ID
                }
            else 
                {
                    Write-Host "$($vm.Name) - BackedUp : No" -BackgroundColor DarkRed
                    [void]$list.Add([PSCustomObject]@{
                    VM_Name = $vm.Name
                    VM_ResourceGroupName = $vm.ResourceGroupName
                    })
                    $vmBackupVault = $null
                    $container =  $null
                    $backupItem =  $null
                }     
[void]$vmBackupReport.Add([PSCustomObject]@{
VM_Name = $vm.Name
VM_Location = $vm.Location
VM_ResourceGroupName = $vm.ResourceGroupName
VM_BackedUp = $recoveryVaultInfo.BackedUp
VM_RecoveryVaultName =  $vmBackupVault.Name
VM_RecoveryVaultPolicy = $backupItem.ProtectionPolicyName
VM_BackupHealthStatus = $backupItem.HealthStatus
VM_BackupProtectionStatus = $backupItem.ProtectionStatus
VM_LastBackupStatus = $backupItem.LastBackupStatus
VM_LastBackupTime = $backupItem.LastBackupTime
VM_BackupDeleteState = $backupItem.DeleteState
VM_BackupLatestRecoveryPoint = $backupItem.LatestRecoveryPoint
VM_Id = $vm.Id
RecoveryVault_ResourceGroupName = $vmBackupVault.ResourceGroupName
RecoveryVault_Location = $vmBackupVault.Location
RecoveryVault_SubscriptionId = $vmBackupVault.ID
})
}
Do{
$choices =@(
	("&E - Exit"),
	("&1 - Export vmBackupReport to CSV"),
	("&2 - View and Assign BU Policy to all VMs"),
	("&3 - View and Assign BU Policy to a single VM")
)
$choicedesc = New-Object System.Collections.ObjectModel.Collection[System.Management.Automation.Host.ChoiceDescription]
for($i=0; $i -lt $choices.length; $i++){
	$choicedesc.Add((New-Object System.Management.Automation.Host.ChoiceDescription $choices[$i] ) ) }
[int]$defchoice = 0
$action = $host.ui.PromptForChoice($title, $prompt, $choices, $defchoice)
Switch ($action)
{
 0 {
		Write-Output "Exited Function."
        Exit
	}
 1 {
		$vmBackupReport | Export-Csv -Path .\vmbackupstatus.csv
        Write-Host "Exported to .\vmbackupstatus.csv!" -ForegroundColor Magenta -BackgroundColor Black
        Exit
	}
 2 {
        $list | Out-String
        if ($list.VM_Name -eq $null)
        {
            Write-Host "Filtered VM List is empty" -ForegroundColor Yellow -BackgroundColor Black
            Write-Host "There are no VM's that need Backup Policy Assigned..." -ForegroundColor Yellow -BackgroundColor Black
            Write-Host ""
        }
        else
        {
            Get-AzRecoveryServicesVault -Name $backupVaults.Name[0] | Set-AzRecoveryServicesVaultContext
            $Pol = Get-AzRecoveryServicesBackupProtectionPolicy -Name "DefaultPolicy"
            $Pol
            Write-Host "Assigning Backup Policy to all VMs" -BackgroundColor DarkBlue
            foreach ($vm in $list){
                $config = Enable-AzRecoveryServicesBackupProtection -Policy $Pol -Name "$($vm.VM_Name)" -ResourceGroupName "$($vm.VM_ResourceGroupName)" | Select-Object -Property "WorkloadName" 
                Write-Host "$($config.WorkloadName) has backup policy $($pol.Name) assigned!" -BackgroundColor -DarkGreen
            }
            Write-Host "Done assigning BU Policy to Resources!" -ForegroundColor Yellow -BackgroundColor Black
            Write-Host ""
        }
 	}
 3 {
        $list | Out-String
        $name = Read-Host -Prompt "Name of VM to be backed up:"
        # loop through VMs in the secondary List and ensure the user entry matches a machine name in the list.  If it doesn't, meaning it has a backup, why are you doing that with this tool?
        foreach ($vm in $list){
            if ($name -match $vm.VM_Name){
                Get-AzRecoveryServicesVault -Name $backupVaults.Name[0] | Set-AzRecoveryServicesVaultContext
                $Pol = Get-AzRecoveryServicesBackupProtectionPolicy -Name "DefaultPolicy"
                $Pol
                $config = Enable-AzRecoveryServicesBackupProtection -Policy $Pol -Name "$($vm.VM_Name)" -ResourceGroupName "$($vm.VM_ResourceGroupName)" | Select-Object -Property "WorkloadName"
                Write-Host "$($config.WorkloadName) has backup policy $($pol.Name) assigned!" -BackgroundColor DarkGreen
            }
            else {
                Write-Host "Entry does not match any Names in Filtered VM List" -ForegroundColor Yellow -BackgroundColor Black
            }
        Write-Host "" -ForegroundColor Yellow -BackgroundColor Black
        }   
    }
}
$repeat = Read-Host "Repeat?"
}
While ($repeat -eq "Y")
Write-Host "EXITING... " -ForegroundColor Yellow -BackgroundColor Black
Write-Host ""
Disconnect-AzAccount > $null
Write-Host "ACCOUNT HAS BEEN DISCONNECTED" -ForegroundColor Yellow -BackgroundColor Black
#end
Featured

AVD Alerts in Terraform

Following up from my post here: https://seehad.tech/2021/08/26/add-robust-monitoring-of-azure-virtual-desktop-using-azure-monitor-alerts/ I've put these alerts into a Terraform module.  You can find the module here: https://github.com/chad-neal/avdtf-with-modules.


module rg {
  source = "../RG"
}
resource "azurerm_monitor_action_group" "email" {
  name                = "Email Desk"
  resource_group_name = module.rg.rg_name
  short_name          = "Email"
  email_receiver {
    name          = "Email"
    email_address = "Azure_Alerts@emaildomain.com"
    use_common_alert_schema = true
  }
}
resource "azurerm_monitor_activity_log_alert" "avd-service-health" {
  name                = "${var.client_name} - AVD Service Health"
  resource_group_name = module.rg.rg_name
  scopes              = [module.rg.rg_id]
  description         = "This alert will monitor AVD Service Health."
  criteria {
    category       = "ServiceHealth"
    service_health {
    events = [
      "Incident", 
      "ActionRequired", 
      "Security"
      ]
    locations = [
      "East US",
      "East US 2",
      "Global",
      "South Central US",
      "West US",
      "West US 2"
      ]
    services = ["Windows Virtual Desktop"]
  }
}
  action {
    action_group_id = azurerm_monitor_action_group.email.id
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-no-resources" {
  name                = "${var.client_name} - AVD 'No available resources'"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will monitor AVD for error 'No Available Resources'."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 1
  frequency           = 15
  time_window         = 5
  query               = <<-QUERY
  WVDErrors
  | where CodeSymbolic == \"ConnectionFailedNoHealthyRdshAvailable\" and Message contains \"Could not find any SessionHost available in specified pool\"
QUERY
  trigger {
    operator          = "GreaterThan"
    threshold         = 20
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-host-mem-below-gb" {
  name                = "${var.client_name} - AVD Available Host Memory below 1GB"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will be triggered when Available Host Memory is less than 1GB."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 2
  frequency           = 15
  time_window         = 5
  query               = <<-QUERY
  Perf
  | where ObjectName == \"Memory\"
  | where CounterName == \"Available Mbytes\"
  | where CounterValue <= 1024
QUERY
  trigger {
    operator          = "GreaterThanOrEqual"
    threshold         = 1
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-failed-connections" {
  name                = "${var.client_name} - AVD Failed Connections"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will be triggered when there's more than 10 failed AVD connections in 15 minutes."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 2
  frequency           = 5
  time_window         = 15
  query               = <<-QUERY
WVDConnections
  | where State =~ \"Started\" and Type =~\"WVDConnections\"
  | extend Multi=split(_ResourceId, \"/\") | extend CState=iff(SessionHostOSVersion==\"<>\",\"Failure\",\"Success\")
  | where CState =~\"Failure\"
  | order by TimeGenerated desc
  | where State =~ \"Started\" | extend Multi=split(_ResourceId, \"/\")
  | project ResourceAlias, ResourceGroup=Multi[4], HostPool=Multi[8], SessionHostName, UserName, CState=iff(SessionHostOSVersion==\"<>\",\"Failure\",\"Success\"), CorrelationId, TimeGenerated
  | join kind= leftouter (WVDErrors) on CorrelationId
  | extend DurationFromLogon=datetime_diff(\"Second\",TimeGenerated1,TimeGenerated)
  | project  TimeStamp=TimeGenerated, DurationFromLogon, UserName, ResourceAlias, SessionHost=SessionHostName, Source, CodeSymbolic, ErrorMessage=Message, ErrorCode=Code, ErrorSource=Source ,ServiceError, CorrelationId
  | order by TimeStamp desc
QUERY
  trigger {
    operator          = "GreaterThanOrEqual"
    threshold         = 10
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-fslogix-errors" {
  name                = "${var.client_name} - AVD FSLogix Errors"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will be triggered when there's more than 1 FSLogix Errors in 5 minutes."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 2
  frequency           = 5
  time_window         = 5
  query               = <<-QUERY
  Event 
  | where EventID == "26" and isnotnull(Message) 
  | where Message != "" 
  | where UserName != "NT AUTHORITY\\SYSTEM" 
  | order by TimeGenerated desc
QUERY
  trigger {
    operator          = "GreaterThanOrEqual"
    threshold         = 1
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-out-of-memory" {
  name                = "${var.client_name} - AVD Host Out of Memory Errors"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will be triggered when there's more than 20 Out of Memory Errors in 30 minutes."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 1
  frequency           = 5
  time_window         = 30
  query               = <<-QUERY
  WVDErrors
  | where CodeSymbolic == \"OutOfMemory\" and Message contains \"The user was disconnected because the session host memory was exhausted.\"
QUERY
  trigger {
    operator          = "GreaterThanOrEqual"
    threshold         = 20
  }
}
resource "azurerm_monitor_scheduled_query_rules_alert" "avd-high-cpu" {
  name                = "${var.client_name} - AVD Host % Proc Time Greater Than 99"
  location            = module.rg.rg_location
  resource_group_name = module.rg.rg_name
  data_source_id      = var.workspace_id
  description         = "This alert will be triggered when there's more than 50 High CPU alerts in 10 minutes."
  action {
    action_group      = azurerm_monitor_action_group.email.id
  }
  enabled             = true
  severity            = 1
  frequency           = 5
  time_window         = 10
  query               = <<-QUERY
  Perf   
  | where CounterName == "% Processor Time"
  | where InstanceName == "_Total"
  | where CounterValue >= 99
QUERY
  trigger {
    operator          = "GreaterThanOrEqual"
    threshold         = 50
  }
}
resource "azurerm_monitor_metric_alert" "avd-pct-proc-pagefile" {
  name                = "${var.client_name} - AVD Pct Processor committed bytes utilization"
  resource_group_name = module.rg.rg_name
  scopes              = var.workspace_id
  description         = "Action will be triggered when Average % of Committed Bytes in Use is greater than 80."
  enabled             = false
  frequency           = "PT5M"
  window_size         = "PT5M"
  severity            = 2
  criteria {
    metric_namespace = "Microsoft.OperationalInsights/workspaces"
    metric_name      = "Average_% Committed Bytes In Use"
    aggregation      = "Maximum"
    operator         = "GreaterThanOrEqual"
    threshold        = 80
    dimension {
      name     = "ApiName"
      operator = "Include"
      values   = ["*"]
    }
  }
  action {
    action_group_id = azurerm_monitor_action_group.email.id
  }
}
resource "azurerm_monitor_metric_alert" "avd-sa-capacity" {
  name                  = "${var.client_name} - AVD Storage Account Capacity Alert"
  resource_group_name   = module.rg.rg_name
  scopes                = var.storageacct_id
  description           = "Action will be triggered when Storage Account Capacity is close to full."
  enabled               = true
  frequency             = "PT5M"
  window_size           = "PT1H"
  severity              = 1
  target_resource_type  = "Microsoft.Storage/storageAccounts/fileServices"
  target_resource_location = var.storageacct_region
  criteria {
    metric_namespace = "microsoft.storage/storageaccounts/fileservices"
    metric_name      = "FileCapacity"
    aggregation      = "Average"
    operator         = "GreaterThanOrEqual"
    threshold        = var.storageacct_threshold_bytes
    dimension {
      name     = "FileShare"
      operator = "Include"
      values   = ["fshare"]
    }
  }
  action {
    action_group_id = azurerm_monitor_action_group.email.id
  }
}

Find corrupt VHDX profiles in Azure Files

Edit (05/24/2022): We finally discovered the cause of all of our Hosts daily stress and alerts.  Including solving this problem.  We switched to using Breadth-first load balancing from Depth-first.  This stopped piling users onto a server before moving to another one, and therefore steadily rose processing and RAM usage instead of spiking it, reducing all kinds of issues.


If you haven't had the pleasure of dealing with this, be thankful. There is some set of parameters which, when met, corrupts a user's mounted VHDX, causing them to receive messages from the OS that their disk needs to be repaired, and/or they're logged in with a temp profile. Once detected, and the user logs off and back on again, FSLogix will create a new VHDX, put it in the same directory, rename the original VHDX and append "CORRUPT" to the beginning of the filename. If you can't tell, this is bad... mmmkay? If you don't have Onedrive or some other backup enabled, the user just lost everything saved to their profile.


I have gone round and round with Microsoft Support about this problem. The conclusion of which is that when an AVD host is heavily utilized to the point of throwing error messages related to CPU or RAM usage/exhaustion, this CAN cause corruption. What the actual set of parameters is that causes this corruption is unknown or not fully understood. Microsoft's recommendation is to add more host resources so it doesn't get to the point of CPU/RAM exhaustion. Fine, fair point, but still, c'mon guys... You own AVD/FSLogix, which means this renaming logic is coded somewhere, and you don't know either?! Doubtful.


Anyways, corrupting profiles is one problem, but what about all these orphaned disks that are lying around, taking up space, and literally can't be fixed, can't be mounted somewhere else, can't be used at all. In some of our deployments, this equalled about 2-3TB of used space. At about $120/100GB/month of provisioned space, this could not be overlooked. So, I took my other script from here: https://seehad.tech/2021/08/24/searching_azure_file_share_to_match_string/ and modified it to search for CORRUPT profiles.