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.

Use PowerShell to interact with REST APIs

API's are quickly becoming foundational for every SaaS product out there. They provide a gateway into interacting with the product without having to go through the exercise of a full integration with the product. You can use all kinds of methods and code languages to interact with APIs. This is just how PowerShell does it.


param(
    [Parameter(Mandatory=$true)]
    [string] $accountEndpoint = "",
    
    [Parameter(Mandatory=$true)]
    [string] $client_id = "",
    
    [Parameter(Mandatory=$true)]
    [string] $client_secret = ""
)
$DateStamp = get-date -uformat "%Y-%m-%d@%H-%M-%S"

$token = Invoke-RestMethod -Method Post -Uri "https://$($accountEndpoint)/auth/connect/token" `
    -Body @{
        grant_type="client_credentials";
        client_id=$client_id;
        client_secret=$client_secret;
        scope="api"
    }

Invoke-RestMethod -Method Get -Uri "https://api.cloudcheckr.com/api/best_practice.json/get_best_practices_v3?access_key=bearer $($token.access_token)&use_account=All%20Azure%20Accounts" | ConvertTo-Json | Out-File ".\data\azure_best_practice_checks_$($DateStamp).json"


Note: Invoke-RestMethod also assumes the output is converted from JSON into PowerShell objects, which is why I needed to convert it back. Invoke-WebRequest can also be used and is better for dealing with HTML results.


This example is to get Best Practice Checks available from Cloudcheckr. Cloudcheckr is a tool used to scan an Azure tenant, read all kinds of information about it, and display that information without having to login to the Azure Portal itself. It provides insight into and checks to ensure Best Practices are followed for things like, Network Security Groups having all inbound ports enabled-which is dumb, don't do dumb shit. It also scans VM's usage properties and offers suggestions for cost savings by reducing a VM's size or the possibility of combining workloads from multiple "idle" VMs. There are other tools out there that do this, like Flexera, and vCommander. These fall into the category of Cloud Management Platforms, and are a layer on top of Cloud resources that orchestrate, but allow a company like a Managed Services Provider to give access to Customer business units without having to onboard them directly into the native cloud environment.