Using PowerShell to query Azure Log Analytics via the REST API

I wanted to pull some data out of Azure Log Analytics using PowerShell and the REST API. Here is the code to Pull all errors in the Application event logs on VMs that are pushing their logs into Log Analytics via MicrosoftMonitoringAgent.

Hopefully this may help someone:

$SubscriptionId = "$($env:SubscriptionId)"
$TenantId       = "$($env:TenantId)" 
$ClientID       = "$($env:ClientID)"      
$ClientSecret   = "$($env:ClientSecret)"  
$TenantDomain   = "$($env:TenantDomain)" 
$loginURL       = "https://login.microsoftonline.com/$TenantId/oauth2/token"
$resource       = "https://api.loganalytics.io"         

$body           = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
$oauth          = Invoke-RestMethod -Method Post -Uri $loginURL -Body $body
$headerParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"}

$Workspacename="Your WS Name"
$WorkspaceId="Your WS ID"

$url="https://api.loganalytics.io/v1/workspaces/$WorkspaceId/query"
$body = @{query = 'Event | where EventLog == "Application" | order by TimeGenerated asc | project Computer,EventLog,Source,EventLevelName,EventID,RenderedDescription,TimeGenerated'} | ConvertTo-Json
$webresults=Invoke-RestMethod -UseBasicParsing -Headers $headerParams -Uri $url -Method Post -Body $body -ContentType "application/json"

Notes:

  1. I keep my subscription information in Env Varaibles. It is easier for me to swtich to a different Tenant
  2. ¬†This returns the results in tables. To move the tables into an object look at this person’s code at line 60¬† https://blog.tyang.org/2017/11/14/searching-oms-using-the-new-search-language-kusto-rest-api-in-powershell/
  3. My interpretation of code in #2
$resultsTable=$webresults.Content | ConvertFrom-Json
$count = 0
foreach ($table in $resultsTable.Tables) {
$count += $table.Rows.Count
}
$results = New-Object object[] $count
$i = 0;
foreach ($table in $resultsTable.Tables) {
    foreach ($row in $table.Rows) {
        # Create a dictionary of properties
        $properties = @{}
        for ($columnNum=0; $columnNum -lt $table.Columns.Count; $columnNum++) {
            $properties[$table.Columns[$columnNum].name] = $row[$columnNum]
        }      
        $results[$i] = (New-Object PSObject -Property $properties)
        $null = $i++
    }
}
$results



,

No comments yet.

Leave a Reply