Query the Log Analytics Workspace for all Azure VM

Fabian Bader included in Azure Log Analytics PowerShell Excel OMS

2019-02-28 299 words 2 minutes

/en/query-the-log-analytics-workspace-for-all-azure-vm/images/2019-02-28-21_34_42-ReportsAndQuickQueries.ps1-ReportsAndCodeSnippets-Visual-Studio-Code.png

Contents

Sometimes you just need to know to which Log Analytics Workspace (OMS for the old folks out there) a VM send it’s data to. Or even all of you Azure VMs an once.

With the following script this task is easy as pie. And thanks to RamblingCookieMonster and his PSExcel modul you can send the result straight to everybody who is fond of Excel.

ReportLogAnalyticsWorkspacePerVM.ps1

#region Report: All VMs including the status, and OMS workplace
$Subscriptions = Get-AzureRmSubscription | Where-Object { $_.Name -in ("SUB01","SUB02") }
$OmsWorkspaces = @()
ForEach ($Subscription in $Subscriptions) {
    $SubscriptionName = $Subscription.Name
    Set-AzureRmContext -SubscriptionName "$SubscriptionName" | Out-Null
    $OmsWorkspaces += Get-AzureRmOperationalInsightsWorkspace
}
$Report = ForEach ($Subscription in $Subscriptions) {
    $SubscriptionName = $Subscription.Name
    Set-AzureRmContext -SubscriptionName "$SubscriptionName" | Out-Null
    $RGs = Get-AzureRMResourceGroup
    foreach ($RG in $RGs) {
        $VMs = Get-AzureRmVM -ResourceGroupName $RG.ResourceGroupName
        foreach ($VM in $VMs) {
            # Query OMS Workspace
            $VM = Get-AzureRmVM -ResourceGroupName $RG.ResourceGroupName -Name $VM.Name
            $ExtensionName = $VM.Extensions | Where-Object { $_.VirtualMachineExtensionType -in ("MicrosoftMonitoringAgent", "OmsAgentForLinux") } | Select-Object -ExpandProperty Name -First 1
            if ( $ExtensionName ) {
                $ExtensionInformation = Get-AzureRmVMExtension -ResourceGroupName $VM.ResourceGroupName -VMName $VM.Name -Name $ExtensionName
                $OmsWorkspaceId = ($ExtensionInformation.PublicSettings | ConvertFrom-Json).workspaceId
                if ( $OmsWorkspaceId ) {
                    $OmsWorkspace = $OmsWorkspaces | Where-Object { $_.CustomerId -eq $OmsWorkspaceId }
                    $OmsWorkspaceName = "$($OmsWorkspace.Name)"
                } else {
                    $OmsWorkspaceName = "n/a"
                    $OmsWorkspaceId = "n/a"
                }
            } else {
                $OmsWorkspaceName = "n/a"
                $OmsWorkspaceId = "n/a"
            }
            # VM Status (running/deallocated/stopped)
            $VMDetail = Get-AzureRmVM -ResourceGroupName $RG.ResourceGroupName -Name $VM.Name -Status
            $VMStatusDetail = $VMDetail.Statuses.DisplayStatus -match "^VM .*$"
            New-Object psobject -Property @{
                "VMName"           = $VM.Name
                "OSType"           = $VM.StorageProfile.OSDisk.OSType
                "ResourceGroup"    = $RG.ResourceGroupName
                "VMStatus"         = "$VMStatusDetail"
                "SubscriptionName" = $SubscriptionName
                "OmsWorkspaceName" = $OmsWorkspaceName
                "OmsWorkspaceId"   = $OmsWorkspaceId
            }
            Remove-Variable -Name OmsWorkspaceId -ErrorAction SilentlyContinue
            Remove-Variable -Name ExtensionName -ErrorAction SilentlyContinue
        }
    }
}
# Export as XLSX file
Import-Module PSExcel
$Filename = "LogAnalytics_Report_" + (Get-Date).ToString("yyyy-MM-dd_HHmmss")
$Report | Export-XLSX -Path "~/$Filename.xlsx" -Table -Autofit