Run-In-CloudShell.ps1

# Run-In-CloudShell.ps1
# This script is optimized to run directly in Azure Cloud Shell
# Usage: Upload this script to Cloud Shell, then run it from PowerShell

param(
    [string]$SubscriptionId,
    [string]$OutputStorageAccount,
    [string]$OutputStorageContainer = "reports",
    [bool]$SaveToCloudDrive = $true,
    [bool]$SQLServerOnly = $true,
    [string]$SqlUsername,
    [securestring]$SqlPassword
)

Write-Host "Azure VM Licensing Report - Cloud Shell Edition" -ForegroundColor Cyan
Write-Host "Environment: $(if ($env:CLOUD_SHELL -eq 'true') { 'Azure Cloud Shell' } else { 'Local PowerShell' })" -ForegroundColor Green
Write-Host "SQL Server Only: $(if ($SQLServerOnly) { 'Yes' } else { 'No' })" -ForegroundColor Green

# Prepare for optional SQL authentication
if (-not $SqlUsername) {
    $promptForAuth = Read-Host "Do you want to use SQL Server authentication for database counting? (Enter 'yes' to provide credentials, or press Enter for Windows auth)"
    
    if ($promptForAuth -eq 'yes') {
        $SqlUsername = Read-Host "Enter SQL Server username"
        $SqlPassword = Read-Host "Enter SQL Server password" -AsSecureString
        Write-Host "SQL Server authentication enabled for database counting." -ForegroundColor Green
    }
}

# Detect Cloud Shell
$isCloudShell = $env:CLOUD_SHELL -eq 'true'

if ($isCloudShell) {
    Write-Host "Cloud Shell detected!" -ForegroundColor Green
    Write-Host "Home directory: $HOME" -ForegroundColor Gray
    Write-Host "Cloud Drive: ~/clouddrive" -ForegroundColor Gray
}

# Create output directory
if ($isCloudShell) {
    $outputDir = "$HOME/clouddrive/azure-reports"
    if (-not (Test-Path $outputDir)) {
        New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
        Write-Host "Created output directory: $outputDir" -ForegroundColor Green
    }
    
    if ($SaveToCloudDrive) {
        Write-Host "Reports will be saved to: $outputDir (persisted in Cloud Drive)" -ForegroundColor Cyan
    }
}
else {
    $outputDir = "/home/$(whoami)/azure-reports"
    if (-not (Test-Path $outputDir)) {
        New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
    }
}

$reportPath = Join-Path $outputDir "Azure_VM_Report_$(Get-Date -Format 'yyyyMMdd_HHmmss').xlsx"

# Function to check if ImportExcel module is installed
function Test-ImportExcelModule {
    if (Get-Module -ListAvailable -Name ImportExcel) {
        return $true
    }
    return $false
}

# Function to install ImportExcel module in Cloud Shell
function Install-ImportExcelModule {
    Write-Host "Installing ImportExcel module..." -ForegroundColor Yellow
    try {
        Install-Module -Name ImportExcel -Force -Scope CurrentUser -SkipPublisherCheck -AllowClobber -ErrorAction Stop
        Write-Host "ImportExcel module installed successfully." -ForegroundColor Green
        return $true
    }
    catch {
        Write-Error "Failed to install ImportExcel module: $_"
        Write-Host "Tip: In Cloud Shell, you might need to run 'Update-Module' first" -ForegroundColor Yellow
        return $false
    }
}

# Check and install ImportExcel if needed
if (-not (Test-ImportExcelModule)) {
    if (-not (Install-ImportExcelModule)) {
        Write-Error "ImportExcel module is required but could not be installed."
        exit 1
    }
}

Import-Module ImportExcel

# Set subscription if provided
if ($SubscriptionId) {
    Write-Host "Setting subscription to: $SubscriptionId" -ForegroundColor Yellow
    az account set --subscription $SubscriptionId
}

# Get current subscription info
$subscriptionInfo = az account show | ConvertFrom-Json
Write-Host "Using subscription: $($subscriptionInfo.name) ($($subscriptionInfo.id))" -ForegroundColor Green

# ============ Main Report Generation Logic ============

Write-Host "Retrieving Azure VMs..." -ForegroundColor Yellow
$vms = az vm list --query '[*].[id,name,resourceGroup,hardwareProfile.vmSize,storageProfile.osDisk.osType]' -o json | ConvertFrom-Json

$vmDetails = @()

# VM Size to vCPU mapping
$vmSizeMap = @{
    "Standard_B1s" = 1; "Standard_B1ms" = 1; "Standard_B2s" = 2; "Standard_B2ms" = 2; "Standard_B4ms" = 4
    "Standard_B12ms" = 12; "Standard_D1_v2" = 1; "Standard_D2_v2" = 2; "Standard_D3_v2" = 4; "Standard_D4_v2" = 8
    "Standard_D5_v2" = 16; "Standard_E2_v3" = 2; "Standard_E4_v3" = 4; "Standard_E8_v3" = 8; "Standard_E16_v3" = 16
    "Standard_E32_v3" = 32; "Standard_F2s_v2" = 2; "Standard_F4s_v2" = 4; "Standard_F8s_v2" = 8; "Standard_F16s_v2" = 16
}

function Get-VMvCPUCount {
    param(
        [string]$VMSize,
        [string]$Location
    )

    if ($vmSizeMap.ContainsKey($VMSize)) {
        return $vmSizeMap[$VMSize]
    }

    if ($Location) {
        try {
            $skuInfo = az vm list-skus --location $Location --resource-type virtualMachines --query "[?name=='$VMSize']" -o json 2>$null | ConvertFrom-Json
            if ($skuInfo -and $skuInfo.Count -gt 0) {
                $vCpuCapability = $skuInfo[0].capabilities | Where-Object { $_.name -eq 'vCPUs' } | Select-Object -First 1
                if ($vCpuCapability -and ($vCpuCapability.value -as [int])) {
                    return [int]$vCpuCapability.value
                }
            }
        }
        catch { }
    }

    return 0
}

function Get-SQLServerInfo {
    param(
        [string]$ImagePublisher,
        [string]$ImageOffer,
        [string]$ImageSku,
        [string]$VmLicenseType,
        [hashtable]$Tags
    )
    
    $sqlInfo = @{
        'HasSQL' = $false
        'SQLEdition' = 'N/A'
        'SQLVersion' = 'N/A'
        'SQLLicense' = 'N/A'
    }
    
    if (($ImagePublisher -like "*microsoftsqlserver*") -or ($ImageOffer -match "sql") -or ($ImageSku -match "sql")) {
        $sqlInfo['HasSQL'] = $true
        
        if ($ImageOffer -match "SQL2025") { $sqlInfo['SQLVersion'] = "SQL Server 2025" }
        elseif ($ImageOffer -match "SQL2022") { $sqlInfo['SQLVersion'] = "SQL Server 2022" }
        elseif ($ImageOffer -match "SQL2019") { $sqlInfo['SQLVersion'] = "SQL Server 2019" }
        elseif ($ImageOffer -match "SQL2017") { $sqlInfo['SQLVersion'] = "SQL Server 2017" }
        elseif ($ImageOffer -match "SQL2016") { $sqlInfo['SQLVersion'] = "SQL Server 2016" }
        elseif ($ImageSku -match "2025") { $sqlInfo['SQLVersion'] = "SQL Server 2025" }
        elseif ($ImageSku -match "2022") { $sqlInfo['SQLVersion'] = "SQL Server 2022" }
        elseif ($ImageSku -match "2019") { $sqlInfo['SQLVersion'] = "SQL Server 2019" }
        elseif ($ImageSku -match "2017") { $sqlInfo['SQLVersion'] = "SQL Server 2017" }
        elseif ($ImageSku -match "2016") { $sqlInfo['SQLVersion'] = "SQL Server 2016" }
        else { $sqlInfo['SQLVersion'] = "SQL Server (Version Unknown)" }
        
        if ($ImageSku -match "enterprise" -or $ImageOffer -match "Enterprise") { $sqlInfo['SQLEdition'] = "Enterprise"; $sqlInfo['SQLLicense'] = "License Required" }
        elseif ($ImageSku -match "standard" -or $ImageOffer -match "Standard") { $sqlInfo['SQLEdition'] = "Standard"; $sqlInfo['SQLLicense'] = "License Required" }
        elseif ($ImageSku -match "express" -or $ImageOffer -match "Express") { $sqlInfo['SQLEdition'] = "Express"; $sqlInfo['SQLLicense'] = "Free (Limited)" }
        elseif ($ImageSku -match "web" -or $ImageOffer -match "Web") { $sqlInfo['SQLEdition'] = "Web"; $sqlInfo['SQLLicense'] = "License Required" }
        elseif ($ImageSku -match "developer" -or $ImageOffer -match "Developer") { $sqlInfo['SQLEdition'] = "Developer"; $sqlInfo['SQLLicense'] = "Free (Dev/Test)" }
        else { $sqlInfo['SQLEdition'] = "Edition Unknown"; $sqlInfo['SQLLicense'] = "License Required" }
        
        if ($VmLicenseType -match "SQL_Server_BYOL") {
            $sqlInfo['SQLLicense'] = "BYOL"
        }
        elseif ($Tags -and $Tags.ContainsKey("SqlLicenseType") -and $Tags["SqlLicenseType"] -eq "BYOL") {
            $sqlInfo['SQLLicense'] = "BYOL"
        }
        elseif ($Tags -and $Tags.ContainsKey("SqlServerLicense")) {
            $sqlInfo['SQLLicense'] = $Tags["SqlServerLicense"]
        }
    }
    
    return $sqlInfo
}

function Get-WindowsLicensingType {
    param(
        [string]$OSType,
        [string]$ImagePublisher,
        [string]$ImageOffer,
        [string]$VmLicenseType,
        [hashtable]$Tags
    )
    
    if ($OSType -ne "Windows") {
        return "N/A"
    }

    if ($VmLicenseType -eq "Windows_Server") {
        return "BYOL"
    }

    if ($Tags -and $Tags.ContainsKey("WindowsLicenseType") -and $Tags["WindowsLicenseType"] -eq "BYOL") {
        return "BYOL"
    }

    if ($ImagePublisher -like "*Microsoft*" -and ($ImageOffer -like "*Windows*" -or $ImageOffer -like "*Server*" -or $ImageOffer -like "*SQL*")) {
        return "License Required"
    }

    return "N/A"
}

function Get-SqlDatabaseCount {
    param(
        [string]$ServerInstance
    )
    
    try {
        # Check if SqlServer module is available
        if (-not (Get-Module -ListAvailable -Name SqlServer)) {
            return $null
        }
        
        $query = "SELECT COUNT(*) as [DBCount] FROM sys.databases WHERE database_id > 4"
        $result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -ErrorAction Stop
        return $result[0].DBCount
    }
    catch {
        return $null
    }
}

# Process VMs
foreach ($vm in $vms) {
    $vmId = $vm[0]
    $vmName = $vm[1]
    $resourceGroup = $vm[2]
    $vmSize = $vm[3]
    $osType = $vm[4]
    
    Write-Host "Processing VM: $vmName" -ForegroundColor Gray
    
    $vmFullInfo = az vm show --ids $vmId --query '[name,hardwareProfile.vmSize,storageProfile.imageReference.publisher,storageProfile.imageReference.offer,storageProfile.imageReference.sku,provisioningState,tags,location,licenseType]' -o json | ConvertFrom-Json

    # az CLI returns tags as PSCustomObject after ConvertFrom-Json; normalize to hashtable for tag lookups.
    $vmTags = @{}
    if ($null -ne $vmFullInfo[6]) {
        if ($vmFullInfo[6] -is [hashtable]) {
            $vmTags = $vmFullInfo[6]
        }
        else {
            foreach ($prop in $vmFullInfo[6].PSObject.Properties) {
                $vmTags[$prop.Name] = $prop.Value
            }
        }
    }
    
    # Get SQL Server info first
    $sqlInfo = Get-SQLServerInfo -ImagePublisher $vmFullInfo[2] -ImageOffer $vmFullInfo[3] -ImageSku $vmFullInfo[4] -VmLicenseType $vmFullInfo[8] -Tags $vmTags
    
    # Skip non-SQL VMs if filtering for SQL only
    if ($SQLServerOnly -and -not $sqlInfo['HasSQL']) {
        Write-Host "  (Skipped - No SQL Server)" -ForegroundColor DarkGray
        continue
    }
    
    $vCPUCount = Get-VMvCPUCount -VMSize $vmSize -Location $vmFullInfo[7]
    $windowsLicense = Get-WindowsLicensingType -OSType $osType -ImagePublisher $vmFullInfo[2] -ImageOffer $vmFullInfo[3] -VmLicenseType $vmFullInfo[8] -Tags $vmTags
    
    # Attempt to get database count if SQL Server is detected and module is available
    $databaseCount = 'N/A'
    if ($sqlInfo['HasSQL'] -and (Get-Module -ListAvailable -Name SqlServer)) {
        if ($SqlUsername -and $SqlPassword) {
            # Use SQL authentication
            $databaseCount = Get-SqlDatabaseCount -ServerInstance $vmName -Username $SqlUsername -Password $SqlPassword -ErrorAction SilentlyContinue
        }
        else {
            # Use Windows Integrated authentication
            $databaseCount = Get-SqlDatabaseCount -ServerInstance $vmName -ErrorAction SilentlyContinue
        }
        if ($null -eq $databaseCount -or $databaseCount -eq 'N/A') { $databaseCount = 'Unable to connect' }
    }
    
    $vmDetails += [PSCustomObject]@{
        'Subscription' = $subscriptionInfo.name
        'Resource Group' = $resourceGroup
        'VM Name' = $vmName
        'VM Size' = $vmSize
        'vCPU Count' = $vCPUCount
        'OS Type' = $osType
        'Publisher' = $vmFullInfo[2]
        'Offer' = $vmFullInfo[3]
        'Windows License' = $windowsLicense
        'Has SQL Server' = if ($sqlInfo['HasSQL']) { 'Yes' } else { 'No' }
        'SQL Version' = $sqlInfo['SQLVersion']
        'SQL Edition' = $sqlInfo['SQLEdition']
        'SQL License' = $sqlInfo['SQLLicense']
        'Database Count' = $databaseCount
        'SQL Enterprise Required' = if ($sqlInfo['HasSQL'] -and $sqlInfo['SQLEdition'] -eq 'Enterprise') { 'Yes' } else { 'No' }
        'Provisioning State' = $vmFullInfo[5]
        'Scan Date' = Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
    }
}

if ($vmDetails.Count -eq 0) {
    Write-Host "No VMs found in the subscription." -ForegroundColor Yellow
}
else {
    Write-Host "Found $($vmDetails.Count) VMs. Creating Excel report..." -ForegroundColor Yellow
    
    # Create Excel file
    $excel = $vmDetails | Export-Excel -Path $reportPath -WorksheetName "VMs" -TableName "VMReport" -AutoSize -TableStyle "Light10" -PassThru
    
    # Format headers
    $ws = $excel.Workbook.Worksheets["VMs"]
    $headerRange = $ws.Cells["A1:Q1"]
    $headerRange.Style.Font.Bold = $true
    $headerRange.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
    $headerRange.Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromArgb(70, 120, 180))
    $headerRange.Style.Font.Color.SetColor([System.Drawing.Color]::White)
    
    $ws.View.FreezePanes(2, 1)

    # Add Yes/No dropdown on SQL Enterprise Required column (N) so reviewers can override
    $sqlErValidation = $ws.DataValidations.AddListValidation("N2:N1048576")
    $sqlErValidation.ShowErrorMessage = $false
    $sqlErValidation.ShowInputMessage = $true
    $sqlErValidation.PromptTitle = "SQL Enterprise Required"
    $sqlErValidation.Prompt = "Select Yes or No"
    $sqlErValidation.Formula.Values.Add("Yes")
    $sqlErValidation.Formula.Values.Add("No")

    # Summary sheet
    $summaryLabel = if ($SQLServerOnly) { 'SQL Server VMs' } else { 'Total VMs' }
    $summaryData = @(
        [PSCustomObject]@{ 'Metric' = $summaryLabel; 'Value' = $vmDetails.Count }
        [PSCustomObject]@{ 'Metric' = 'Total vCPUs'; 'Value' = ($vmDetails | Measure-Object -Property 'vCPU Count' -Sum).Sum }
        [PSCustomObject]@{ 'Metric' = 'Windows VMs'; 'Value' = ($vmDetails | Where-Object { $_.'OS Type' -eq 'Windows' }).Count }
        [PSCustomObject]@{ 'Metric' = 'Linux VMs'; 'Value' = ($vmDetails | Where-Object { $_.'OS Type' -eq 'Linux' }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server 2025 Instances'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Version' -like "*2025*" }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server 2022 Instances'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Version' -like "*2022*" }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server 2019 Instances'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Version' -like "*2019*" }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server Enterprise Edition'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Edition' -eq 'Enterprise' }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Enterprise Required (Yes)'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Enterprise Required' -eq 'Yes' }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server Standard Edition'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Edition' -eq 'Standard' }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server Developer Edition'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Edition' -eq 'Developer' }).Count }
        [PSCustomObject]@{ 'Metric' = 'SQL Server Express Edition'; 'Value' = ($vmDetails | Where-Object { $_.'SQL Edition' -eq 'Express' }).Count }
        [PSCustomObject]@{ 'Metric' = 'Report Generated'; 'Value' = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' }
    )
    
    $summaryData | Export-Excel -ExcelPackage $excel -WorksheetName "Summary" -AutoSize -TableName "Summary" -TableStyle "Light10" -PassThru > $null
    
    $summarySh = $excel.Workbook.Worksheets["Summary"]
    $summaryHeaders = $summarySh.Cells["A1:B1"]
    $summaryHeaders.Style.Font.Bold = $true
    $summaryHeaders.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
    $summaryHeaders.Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromArgb(70, 120, 180))
    $summaryHeaders.Style.Font.Color.SetColor([System.Drawing.Color]::White)
    
    $excel.Save()
    $excel.Dispose()
    
    Write-Host "Report generated successfully!" -ForegroundColor Green
    Write-Host "Output file: $reportPath" -ForegroundColor Cyan
    
    # Upload to storage if specified
    if ($OutputStorageAccount) {
        Write-Host "`nUploading report to Azure Storage..." -ForegroundColor Yellow
        try {
            az storage blob upload -f $reportPath -c $OutputStorageContainer -n "$(Split-Path $reportPath -Leaf)" --account-name $OutputStorageAccount
            Write-Host "Report uploaded to: https://$OutputStorageAccount.blob.core.windows.net/$OutputStorageContainer/$(Split-Path $reportPath -Leaf)" -ForegroundColor Green
        }
        catch {
            Write-Warning "Could not upload to storage: $_"
        }
    }
}