SOLVED

Ingested GB per month Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2460583%22%20slang%3D%22en-US%22%3EIngested%20GB%20per%20month%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2460583%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20kql%20query%20to%20get%20the%20AVG%20of%20the%20ingested%20GBs%20per%20month%20(only%20billable%20data).%20This%20is%20the%20query%20im%20building%20up%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20currentmonth%3Dmonthofyear(now())%3B%3CBR%20%2F%3Elet%20month1%3Dunion%20*%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(124d)%3CBR%20%2F%3E%7C%20where%20_IsBillable%20%3D%3D%20%22True%22%3CBR%20%2F%3E%7C%20where%20monthofyear(_TimeReceived)%3D%3Dcurrentmonth%3CBR%20%2F%3E%7C%20summarize%20TotalGBytes%20%3Dround(sum(_BilledSize%2F(1024*1024*1024))%2C2)%3CBR%20%2F%3Eby%20bin%20(TimeGenerated%2C%201d)%3CBR%20%2F%3E%7C%20summarize%20avg(TotalGBytes)%20by%20month%3B%3CBR%20%2F%3Elet%20month2%3Dunion%20*%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(124d)%3CBR%20%2F%3E%7C%20where%20_IsBillable%20%3D%3D%20%22True%22%3CBR%20%2F%3E%7C%20where%20monthofyear(_TimeReceived)%3D%3Dcurrentmonth-1%3CBR%20%2F%3E%7C%20summarize%20TotalGBytes%20%3Dround(sum(_BilledSize%2F(1024*1024*1024))%2C2)%3CBR%20%2F%3Eby%20bin%20(TimeGenerated%2C%201d)%3CBR%20%2F%3E%7C%20summarize%20avg(TotalGBytes)%20by%20month%3B%3CBR%20%2F%3Elet%20month3%3Dunion%20*%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(124d)%3CBR%20%2F%3E%7C%20where%20_IsBillable%20%3D%3D%20%22True%22%3CBR%20%2F%3E%7C%20where%20monthofyear(_TimeReceived)%3D%3Dcurrentmonth-2%3CBR%20%2F%3E%7C%20summarize%20TotalGBytes%20%3Dround(sum(_BilledSize%2F(1024*1024*1024))%2C2)%3CBR%20%2F%3Eby%20bin%20(TimeGenerated%2C%201d)%3CBR%20%2F%3E%7C%20summarize%20avg(TotalGBytes)%20by%20month%3B%3CBR%20%2F%3Emonth1%20%7C%20union%20month2%2C%20month3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20getting%20%22'summarize'%20operator%3A%20Failed%20to%20resolve%20scalar%20expression%20named%20'month'%22%20every%20time%20i%20run%20the%20query.%20Any%20idea%20how%20to%20solve%20this%20situation%20or%20any%20suggestion%20about%20how%20can%20i%20get%20the%20date%20i%20need%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2460583%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2463153%22%20slang%3D%22en-US%22%3ERe%3A%20Ingested%20GB%20per%20month%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1081833%22%20target%3D%22_blank%22%3E%40maynardsAH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20query%20will%20be%20many%20magnitudes%20faster%20if%20you%20use%20the%20%3CSTRONG%3EUsage%3C%2FSTRONG%3E%20table%20(which%20has%20already%20aggregated%20the%20usage%20data)%2C%20rather%20than%20trawling%20through%20a%20massive%20number%20of%20records.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20not%20worried%20about%20whole%20month%2C%20this%20is%20a%20simple%20query%20for%20each%2030d%20period%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EUsage%0A%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-3)..%20endofmonth(now()%2C-1)%20)%20%0A%7C%20summarize%20GBday%20%3D%20sum(Quantity)%2F1000%20by%20bin(TimeGenerated%2C%2030d)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%23%40eb81c4c9-2546-43f2-8c43-9c2295af4b88%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FLogsBlade%2FresourceId%2F%252Fsubscriptions%252F0208bc97-b7dc-4866-8b2b-c4725af15834%252FresourceGroups%252Fgbb_sentinel%252Fproviders%252FMicrosoft.OperationalInsights%252Fworkspaces%252Fgbbsentinel%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA13NsQ7CIBRG4Z2n%25252BEduUitNZxeXzib6ABCuloFLQm9DMD68Our6DefcNv9g80JbuTKuKfPCwtUrRwTWxiyw2NRXLfdcRFcrpVkaDjONI1jiH08Ewie47Tn7mp6M5Rx9x%25252Bkr9rJ70aSdjpNzDqEjJLE%25252F2wGzi2TMG3OCnaWaAAAA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ETimeGenerated%3C%2FTH%3E%0A%3CTH%3EGBday%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2021-03-02T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E516.7549875128676%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2021-05-01T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E1060.202420264586%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2021-04-01T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E520.8967723819818%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2021-05-31T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E34.28783515715939%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2021-01-31T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E2.716749379860088%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3Bor%20to%20have%20whole%20months%2C%20maybe%20this%20is%20a%20start%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22language-cpp%22%3E%3CCODE%3Eunion%0A(%0A%20%20%20%20Usage%0A%20%20%20%20%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-3)..%20endofmonth(now()%2C-3)%20)%20%0A%20%20%20%20%7C%20summarize%20GBmonth%3Dsum(Quantity)%2F1000%20%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%2C%20month%20%3D%20datetime_part(%22month%22%2C%20min(TimeGenerated))%0A)%2C%0A(%0A%20%20%20%20Usage%0A%20%20%20%20%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-2)..%20endofmonth(now()%2C-2)%20)%20%0A%20%20%20%20%7C%20summarize%20GBmonth%3Dsum(Quantity)%2F1000%20%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%2C%20month%20%3D%20datetime_part(%22month%22%2C%20min(TimeGenerated))%0A)%2C%0A(%0A%20%20%20%20Usage%0A%20%20%20%20%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-1)..%20endofmonth(now()%2C-1)%20)%20%0A%20%20%20%20%7C%20summarize%20GBmonth%3Dsum(Quantity)%2F1000%20%2C%20min(TimeGenerated)%2C%20max(TimeGenerated)%2C%20month%20%3D%20datetime_part(%22month%22%2C%20min(TimeGenerated))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EGBmonth%3C%2FTH%3E%0A%3CTH%3Emin_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emax_TimeGenerated%3C%2FTH%3E%0A%3CTH%3Emonth%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E519.4717368927277%3C%2FTD%3E%0A%3CTD%3E2021-03-01T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E2021-03-31T23%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E3%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E520.8967723819818%3C%2FTD%3E%0A%3CTD%3E2021-04-01T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E2021-04-30T23%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E4%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1094.4902554217456%3C%2FTD%3E%0A%3CTD%3E2021-05-01T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E2021-05-31T23%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3E5%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2466464%22%20slang%3D%22en-US%22%3ERe%3A%20Ingested%20GB%20per%20month%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2466464%22%20slang%3D%22en-US%22%3ETnks%20a%20lot%20Clive%2C%20the%20query's%20numbers%20looks%20pretty%20good.%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by maynardsAH (New Contributor)
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



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