Forum Discussion

Copper Contributor

Jun 17, 2021

Solved

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...

Jun 18, 2021

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

dullinternet_1989

Copper Contributor

Jan 12, 2024

Good morning,

I am also trying to find the same information out except that I need to narrow it down by subscription. The query listed runs perfectly for all subscriptions when I run it, however as soon I select 4 individual subscriptions I get an error "'where' operator: Failed to resolve table or column expression named 'Usage' Request id: 323b9391-1abc-4014-ac0c-5435d31b30df"

Could somebody please walk me through the process of how to get this table to populate? If I am correct, I think it means that the table "Usage" does not exist for these subscriptions therefore the query can't pull the data?

CliveWatson

Clive_Watson

Bronze Contributor

Jan 12, 2024

dullinternet_1989

Usage doesnt hold the subscription information you need, so you have to revert to the union * command, so it will be slower to run.

union
(
    union *
    | where _IsBillable == "True"
    | where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-3) ) 
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
),
(
    union *
    | where TimeGenerated between ( startofmonth(now(),-2).. endofmonth(now(),-2) ) 
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
),
(
    union *
    | where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
)

Remember its possible for there to be no SubscriptionID so you often get an empty column. If you add in a filter for the subscription you want, it should look like this - please not I haven't tested this very well, so please test and amend.

let findSubIds = dynamic(['sub id 1','sub 2',' Sub 3','']);
union
(
    union *
    | where _IsBillable == "True"
    | where TimeGenerated between ( startofmonth(now(),-3).. endofmonth(now(),-3) ) 
    | where SubscriptionId in(findSubIds)
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
),
(
    union *
    | where TimeGenerated between ( startofmonth(now(),-2).. endofmonth(now(),-2) ) 
    | where SubscriptionId in(findSubIds)
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
),
(
    union *
    | where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
    | where SubscriptionId in(findSubIds)
    | summarize GBmonth=round(sum(_BilledSize)/(1024*1024*1024),2), min(TimeGenerated), max(TimeGenerated), month = datetime_part("month", min(TimeGenerated)) by SubscriptionId
)

maynardsAH
Copper Contributor
Jun 21, 2021
Tnks a lot Clive, the query's numbers looks pretty good.