Using Azure Automation, OMS and Storage Tables to capture Configuration Data of Azure VMs Part Two

Well it’s been a long time since part one of this blog and much has changed with OMS Log Analytics. Namely, the query language has transitioned to Kusto and the webhook format that OMS sends to Azure Automation has changed.

The webhook payload change is very different if the Log Analytics workspace has been upgraded to Kusto (or if it is a new workspace). Examples of this payload can be found here. The method to consume this webhook data in PowerShell is below. The meat of it is lines 67-83, but just as important (and similar to the old method) is accepting the webhook payload via a PowerShell parameter.

 
[sourcecode language='powershell'  padlinenumbers='true' wraplines='false']
<# 
 
     .SYNOPSIS 
  
         vmcreate_v2.0.ps1 is an Azure Automation Powershell Runbook 
       .DESCRIPTION 
  
     This script recieves webhook data from OMS based on Azure Activity Logs recording a VM create 
     It will record the basic CMDB data and write it to the automation account output and to an azure storage table with the write-cmdbdata function     
 
 
    .EXAMPLE 
          This should be called by OMS based on an activity log search. See blogs.technet.microsoft.com/knightly 
 
     
 
    .NOTES 
  v 1.2 checks for vnet peering and only writes to the table if the vnet is peered. It also records the source image name.
  v 2.0 is updated for new webhook format of an array of tables containing the vm information 
   #> 


param ( 
    [object]$WebhookData
)

$RequestBody = ConvertFrom-JSON -InputObject $WebhookData.RequestBody
$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

    "Logging in to Azure..."
    Add-AzureRmAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}
 
#Get all metadata properties    
$AlertRuleName = $RequestBody.AlertRuleName
$AlertThresholdOperator = $RequestBody.AlertThresholdOperator
$AlertThresholdValue = $RequestBody.AlertThresholdValue
$AlertDescription = $RequestBody.Description
$LinktoSearchResults =$RequestBody.LinkToSearchResults
$ResultCount =$RequestBody.ResultCount
$Severity = $RequestBody.Severity
$SearchQuery = $RequestBody.SearchQuery
$WorkspaceID = $RequestBody.WorkspaceId
$SearchWindowStartTime = $RequestBody.SearchIntervalStartTimeUtc
$SearchWindowEndTime = $RequestBody.SearchIntervalEndtimeUtc
$SearchWindowInterval = $RequestBody.SearchIntervalInSeconds

# Get detailed search results
if($RequestBody.SearchResult -ne $null)
{
    $SearchResultRows    = $RequestBody.SearchResult.tables[0].rows 
    $SearchResultColumns = $RequestBody.SearchResult.tables[0].columns;

    foreach ($SearchResultRow in $SearchResultRows)
    {   
        $Column = 0
        $Record = New-Object -TypeName PSObject 

        foreach ($SearchResultColumn in $SearchResultColumns)
        {
            $Name = $SearchResultColumn.name
            $ColumnValue = $SearchResultRow[$Column]
            $Record | Add-Member -MemberType NoteProperty -Name $name -Value $ColumnValue -Force
            $Column++
        }
$resourceID = $record.resourceID
$vmname = $record.resource
$rgname = $record.resourcegroup
$subID = $record.subscriptionID 
$caller = $record.caller
write-output $vmname + 'in resource group ' + $rgname 'in sub' + $subID + 'was created'
Select-AzureRmSubscription -SubscriptionId $SubId
$vminfo = Get-AzureRmvm -Name $vmname -ResourceGroupName $Rgname
$vmsize = $vminfo.HardwareProfile.vmsize
$nic = $vminfo.NetworkProfile.NetworkInterfaces
$string = $nic.id.ToString()
$nicname = $string.split("/")[-1]
$ipconfig = Get-AzureRmNetworkInterface -ResourceGroupName $rgname -Name $nicname
$subnet = $ipconfig.ipconfigurations.subnet.id.ToString()
$ipconfig = $ipconfig.IpConfigurations.privateipaddress
$vnet = $subnet.split("/")[-3]
$name = $vminfo.Name
$ostype = $vminfo.StorageProfile.OsDisk.OsType 
$location = $vminfo.location

#imageref is null if marketplace image was used
$imageref = $vminfo.StorageProfile.ImageReference.id
            if ($imageref -ne $null)
                {$sourceimg = $imageref.Split("/")[-1]}
                   else {$sourceimg = 'marketplace'}
$subname = (Get-AzureRmSubscription -SubscriptionId $subid).SubscriptionName

            
#check to see if the VM is on an ER conencted VNET by checking its peering
$peer=Get-AzureRmVirtualNetwork -Name $vnet -ResourceGroupName $rgname
$peer= $peer.VirtualNetworkPeerings

if ($peer.count -gt 0) #only write this data to the storage table if the network is peered (only peer'd vnets can talk to ER vnets)
  {
      
 

   
    #writing output into the automation account for debugging
    write-output "$vmsize $Ipconfig $location $name $ostype $caller $timestamp $subname, $Vnet, $LID"

    #once VM information is collected, it can be written into a storage table 
    Select-AzureRmSubscription -SubscriptionName 'Sub1' #this should be the subscription that owns the storage account, not where the VM is deployed
    $resourceGroup = "RGNAME" #resource group that contains the storage table
    $storageAccount = "cmdbtable" #storage account that contains the table
    $tableName = "CMData"
    $saContext = (Get-AzureRmStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
    $table = Get-AzureStorageTable -Name $tableName -Context $saContext 

 
    #search the storage table to see if the VM already exists
    [string]$filter1 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("ResourceID", [Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal, "$resourceID")
    $new = Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter1 
    if ($new -eq $null) {
        $partitionKey = "VMcreates"
        Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"SourceIMG" = "$sourceIMG"; "SubscriptionID" = "$subid"; "ResourceGroup" = "$rgname"; "ResourceID" = "$resourceID"; "computerName" = "$vmname"; "ostype" = "$ostype"; "CreatorID" = "$caller"; "PrivateIP" = "$IPconfig"; "Vnet"="$Vnet";"Location" = "$Location"}
          
    }
    else {
        $partitionKey = "VMUpdates"
        Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"SourceIMG" = "$sourceIMG"; "SubscriptionID" = "$subid"; "ResourceGroup" = "$rgname"; "ResourceID" = "$resourceID"; "computerName" = "$vmname"; "ostype" = "$ostype"; "CreatorID" = "$caller"; "PrivateIP" = "$IPconfig";"Vnet"="$Vnet"; "Location" = "$Location"}
          
    }

}}  }