CODEWITHSUNDEEP
kql
user211245
user211245

Reputation: 51

KQL data trends per day from multiple log analytics workspaces

Got multiple log analytics workspaces. Running this KQL query to get a summarized data value across all workspaces/tables.

 let timeVal = 31d;
union withsource= table *
| where TimeGenerated > ago(timeVal)
| summarize Size = sum(_BilledSize) by _IsBillable | sort by Size | extend Size2 = format_bytes(Size, 2)
| where _IsBillable = true
| project ['Size'] = Size2, ['IsBillable'] = _IsBillable

This seams to work. Now, would like to discover volume trends per day and came across this here https://learn.microsoft.com/en-us/azure/azure-monitor/logs/analyze-usage#data-volume-trends-for-workspace-based-resources

Modified query to include the bin function:

let timeVal = 31d;
union withsource= table *
| where TimeGenerated > ago(timeVal) 
| summarize Size = sum(_BilledSize) by _IsBillable, bin(TimeGenerated, 1d) | sort by Size | extend Size2 = format_bytes(Size, 2) | where _IsBillable == true
| project  ['Size'] = Size2, ['IsBillable'] = _IsBillable

REsults from 2nd query

Question is, is this a “okay” way of doing this ? Also, how could I potentially include a left column that would hold value such as “Day 1” etc.?

Thanks !

I’ve modified this a bit with hints from https://learn.microsoft.com/en-us/azure/azure-monitor/logs/analyze-usage#send-alert-when-data-collection-is-high

let timeVal = 31d;
Usage 
| where TimeGenerated > ago(timeVal)
| where IsBillable == true 
| summarize DataGB = sum(Quantity / 1000) by bin(TimeGenerated,1d)
| render timechart title = "Daily ingestion in GB"

2nd pic

This at least appears to answer the 2nd part of my question.

EDIT2:The 2nd query provided in the answer returns for me just this

Proposed solution result

Upvotes: 0

Views: 1639

Answers (1)

TheAlistairRoss
TheAlistairRoss

Reputation: 331

You can do this a few ways depending on what you are trying to achieve. If you just wanted a simple query, you could run this. This query is very similar to yours, but changing the TimeGenerated for a day label.

let TimeRangeStart = ago(31d);
Usage 
| where TimeGenerated >= TimeRangeStart
| where IsBillable == true 
| summarize DataGB = sum(Quantity / 1000) by bin(TimeGenerated, 1d)
| order by TimeGenerated asc 
| extend Day = strcat("Day ", row_number())
| project Day, DataGB
| render timechart with (title = "Daily ingestion in GB")

The problem with this is you get incomplete days unless you specify the start and end of days.

enter image description here

Here in the next example I have calculated only days that have a full days worth of data in the time range

let TimeRangeStartParam = todatetime(ago(31d));
let TimeRangeEndParam = todatetime(now());
let TimeRangeStart = iif(TimeRangeStartParam == startofday(TimeRangeStartParam), TimeRangeStartParam, startofday(TimeRangeStartParam) + 1d);
let TimeRangeEnd = iif(TimeRangeEndParam == endofday(TimeRangeEndParam), TimeRangeEndParam, endofday(TimeRangeEndParam) - 1d);
Usage 
| where TimeGenerated between (TimeRangeStart .. TimeRangeEnd)
| where IsBillable == true 
| summarize DataGB = sum(Quantity / 1000) by bin(TimeGenerated, 1d), TenantId
| order by TimeGenerated asc 
| extend Day = strcat("Day ", row_number())
| project Day, DataGB
| render timechart with (title = "Daily ingestion in GB")

You can see the differences in the chart and how it scales with the tails removed.

enter image description here

If you want better visualisations, then I would use Azure Monitor Workbooks to visualise the data and modify them for your exact scenario. There are multiple workspace usage reports in Azure Monitor and Microsoft Sentinel which you can use, though they may need some slight modifications if you need to use multiple workspaces in a single chart.

kind regards

Alistair

Upvotes: 0

Related Questions