How to write KQL to display each Log Analytic Workspace log usage on Azure

Question

I was able to run this Kusto - KQL query in a specific log workspace to find its billable log usages over the last 30 days.

But our subscription has hundreds of log workspaces. Is it possible to write a Kusto query in Azure Log query to display each workspace and their total billable log usage number?

Answer 1

After a lot of workarounds on your requirement, I found that it is not exactly possible to retrieve all the workspace, usage details under a tenant within a single query.

As @Niclas suggested, you can use workspace() function but this doesn't works for all the scenarios.

I tried filtering your query by resourceuri and it worked as below.

Instead of using query language, I would suggest you use PowerShell script which is shown below to get all the required details in a single attempt.

$subscriptions = Get-AzSubscription 
foreach ($sub in $subscriptions) 
{ 
Select-AzSubscription -SubscriptionId $sub.Id
$workspaces = Get-AzOperationalInsightsWorkspace
foreach ($workspacename in $workspaces) {
         $usage = Get-AzConsumptionUsageDetail  -ResourceGroup $workspace.ResourceGroupName -StartDate (Get-Date).AddDays(-30) -EndDate (Get-Date) | Where-Object { $_.MeterCategory -eq "Logs" }
        [PSCustomObject]@{
            Tenant   = $subscription.TenantId
          Workspace = $workspacename.Name
            Logs  = ($usage | Measure-Object -Property UsageQuantity -Sum).Sum
       }
    }
}