Forum Discussion

craigedv's avatar
craigedv
Copper Contributor
Oct 22, 2024

Queries on Log Analytics

Hi All,

 

I was hoping someone would be able to assist me, or at least point me in the right direction.

We have a number of Log Analytics Workspaces generating obvious costs every month.

What I would like to do is the following via script (PowerShell or KQL for instance):

 

1. I would like to pull the costs for the Log Analytics Workspaces and be able to show them all on a single graph (exporting to *.csv would allow me to do so).

2. I would like to pull a report on a LAW and align the costs for that workspace with the resources using it. (the current options with Cost Management + Billing only shows log ingestion).

 

Has anyone been able to do something similar?

Thanks!

CV

2 Replies

  • balasubramanim's avatar
    balasubramanim
    Iron Contributor
    Oct 23, 2024

    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.

Resources