Ingested GB per month Query

Hi all. I am trying to create a kql query to get the AVG of the ingested GBs per month (only billable data). This is the query im building up: let currentmonth=monthofyear(now()); let month1...

azure monitor

Log Analytics

CliveWatson

Jun 18, 2021

maynardsAH

The query will be many magnitudes faster if you use the Usage table (which has already aggregated the usage data), rather than trawling through a massive number of records.

If you are not worried about whole month, this is a simple query for each 30d period

Usage
| where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-1) ) 
| summarize GBday = sum(Quantity)/1000 by bin(TimeGenerated, 30d)

Go to Log Analytics and run query

TimeGenerated	GBday
2021-03-02T00:00:00Z	516.7549875128676
2021-05-01T00:00:00Z	1060.202420264586
2021-04-01T00:00:00Z	520.8967723819818
2021-05-31T00:00:00Z	34.28783515715939
2021-01-31T00:00:00Z	2.716749379860088

or to have whole months, maybe this is a start:

union
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-3) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
),
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-2).. endofmonth(now(),-2) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
),
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
)

GBmonth	min_TimeGenerated	max_TimeGenerated	month
519.4717368927277	2021-03-01T00:00:00Z	2021-03-31T23:00:00Z	3
520.8967723819818	2021-04-01T00:00:00Z	2021-04-30T23:00:00Z	4
1094.4902554217456	2021-05-01T00:00:00Z	2021-05-31T23:00:00Z	5

CliveWatson

Former Employee

Jun 18, 2021

maynardsAH

The query will be many magnitudes faster if you use the Usage table (which has already aggregated the usage data), rather than trawling through a massive number of records.

If you are not worried about whole month, this is a simple query for each 30d period

Usage
| where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-1) ) 
| summarize GBday = sum(Quantity)/1000 by bin(TimeGenerated, 30d)

Go to Log Analytics and run query

TimeGenerated	GBday
2021-03-02T00:00:00Z	516.7549875128676
2021-05-01T00:00:00Z	1060.202420264586
2021-04-01T00:00:00Z	520.8967723819818
2021-05-31T00:00:00Z	34.28783515715939
2021-01-31T00:00:00Z	2.716749379860088

or to have whole months, maybe this is a start:

union
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-3) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
),
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-2).. endofmonth(now(),-2) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
),
(
    Usage
    | where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
    | summarize GBmonth=sum(Quantity)/1000 , min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated))
)

GBmonth	min_TimeGenerated	max_TimeGenerated	month
519.4717368927277	2021-03-01T00:00:00Z	2021-03-31T23:00:00Z	3
520.8967723819818	2021-04-01T00:00:00Z	2021-04-30T23:00:00Z	4
1094.4902554217456	2021-05-01T00:00:00Z	2021-05-31T23:00:00Z	5

maynardsAH

Copper Contributor

Jun 21, 2021

Tnks a lot Clive, the query's numbers looks pretty good.

Forum Discussion

Ingested GB per month Query

Resources