Queries on Log Analytics

craigedv 

Please try these steps, I recently went through the process of pulling costs for Log Analytics Workspaces and aligning them with the resources using them, and here’s a step-by-step guide based on what I did.
1. Pull Costs for Log Analytics Workspaces
Use this PowerShell script to pull the costs of all Log Analytics Workspaces and export them to a CSV

Connect-AzAccount
$workspaces = Get-AzOperationalInsightsWorkspace
$outputCsv = "C:\LAWCosts.csv"
$costs = @()

foreach ($workspace in $workspaces) {
$costData = Get-AzConsumptionUsageDetail -ResourceId $workspace.ResourceId -StartDate (Get-Date).AddDays(-30) -EndDate (Get-Date)
foreach ($entry in $costData) {
$costs += [pscustomobject]@{
WorkspaceName = $workspace.Name
ResourceName = $entry.ResourceName
UsageDate = $entry.UsageStart
Cost = $entry.PretaxCost
}
}
}

$costs | Export-Csv -Path $outputCsv -NoTypeInformation

2. Align Costs with Resources Using the Workspace
Use this KQL query in Azure Monitor Logs to get the resource-wise data ingestion for a Log Analytics Workspace:
KQL (Kusto Query Language) is used to query data in Azure Monitor, Log Analytics, and other Azure services.

AzureDiagnostics
| summarize IngestionSize = sum(_BilledSize) by ResourceId, Resource
| order by IngestionSize desc

Combining Both Reports:
Cost: The PowerShell script gives you the monthly costs for each workspace.
Resource Usage: The KQL query gives you the resource-wise ingestion data.

I merged both datasets in Excel, you can use Power BI as well, we can create a detailed report that aligns Log Analytics costs with the resources using each workspace.