1

I have been asked to use Powershell to query Azure WAS logs for blocked requests. I found https://cloudrobots.net/2021/03/07/download-azure-wav-v2-blocking-logs-w-powershell/ but am having some trouble.

First, I created a new service principal and assigned it the Contributor role assignment. I also created a secret and granted it "AuditLog.Read.All" API permission.

Now I am using the following code:

$TenantId = '<tenant id>'
$AzureADCred = Get-Credential -UserName <tenant id> -Message "Enter secret value"
Connect-AzAccount -ServicePrincipal -Credential $AzureADCred -TenantId $TenantId

$WorkspaceID = '<workspace id>'
$UserPrincipalName = 'user@domain.com'

#Create the query for log analytics workspace for last sign in for user which goes back 180 days
$query = 'SigninLogs | Where-Object TimeGenerated > ago(180d) | Where-Object UserPrincipalName == "' + $UserPrincipalName + '" | summarize signInCount = count() by UserPrincipalName | Sort-Object by signInCount desc'

#Create the query for log analytics workspace for top matched rules
$query = 'AzureDiagnostics | where ResourceProvider == "MICROSOFT.NETWORK" and Category == "ApplicationGatewayFirewallLog" | summarize count() by ruleId_s, bin(TimeGenerated, 1m) | where count_ > 10 | render timechart'

$result = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $query

Disconnect-AzAccount

But I only get back:

Invoke-AzOperationalInsightsQuery : Operation returned an invalid status code 'BadRequest'

What gives?

StackExchangeGuy
  • 741
  • 16
  • 36
  • 1
    Use `Get-Error` to see the error message that is provided by the API. I assume it will hint that your query is invalid, because you query must be a Kusto query. `where-object` is not valid kusto, but `where` is. – vrdse Nov 08 '21 at 19:46
  • Hello @StackExchangeGuy, may i know if the issue was resolved following the suggestion of @vrdse? – Ansuman Bal Nov 09 '21 at 11:18

0 Answers0