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

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
)