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"
    }
  }
}

Leave a comment