Forum Discussion

maynardsAH's avatar
maynardsAH
Copper Contributor
Jun 17, 2021

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

  • 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 

     

    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's avatar
      dullinternet_1989
      Copper Contributor

      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's avatar
        Clive_Watson
        Bronze Contributor

        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's avatar
      maynardsAH
      Copper Contributor
      Tnks a lot Clive, the query's numbers looks pretty good.

Resources