Ingested GB per month Query

[maynardsAH]

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=union *
| where TimeGenerated > ago(124d)
| where _IsBillable == "True"
| where monthofyear(_TimeReceived)==currentmonth
| summarize TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)
by bin (TimeGenerated, 1d)
| summarize avg(TotalGBytes) by month;
let month2=union *
| where TimeGenerated > ago(124d)
| where _IsBillable == "True"
| where monthofyear(_TimeReceived)==currentmonth-1
| summarize TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)
by bin (TimeGenerated, 1d)
| summarize avg(TotalGBytes) by month;
let month3=union *
| where TimeGenerated > ago(124d)
| where _IsBillable == "True"
| where monthofyear(_TimeReceived)==currentmonth-2
| summarize TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)
by bin (TimeGenerated, 1d)
| summarize avg(TotalGBytes) by month;
month1 | union month2, month3

but getting "'summarize' operator: Failed to resolve scalar expression named 'month'" every time i run the query. Any idea how to solve this situation or any suggestion about how can i get the date i need?

thanks in advance

[#2 · Accepted solution]

@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

[#3]

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