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