KQL - Automatic date calc to get full month?

Question

Hello,

I'm using the query below in a workbook with time range filter to determine the average gb per day in the workspace:

union withsource = tt *
| summarize
TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)
by bin(TimeGenerated, 1d)//, Solution=tt
| summarize round(avg(TotalGBytes),2)

I want to create a playbook sending a notification to me every new month at 00:00, and to send me the calculations from previous month from 12:00:00 AM(30 or 31 days) to beginning of 12:00:00AM current month.

Example: KQL fires 1st of January and sends me the calculations for December from: 1st December 12:00:00AM to 1st January 12:00:00AM

I tried achieving somewhat of what I need using the below , however there are like 2-3GBs per day difference from when I pick up 1st November 12:00:00AM to 1st December 12:00:00AM - I need exact numbers.

// Automatic date calc to get full month
let lastmonthNumber = getmonth(datetime(now)) - 1;
let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber);
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
let dateStart = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateStart);
union withsource = tt *
| where TimeGenerated between(dateStart .. dateEnd)
| summarize
TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)
by bin(TimeGenerated, 1d)//, Solution=tt
| summarize round(avg(TotalGBytes),2)

Answer 1

I'm not sure where you think the 2-3GB is missing?

Also you can simplify this in two ways:

union withsource = tt *  
| where TimeGenerated between(startofmonth(now(),-1) .. endofmonth(now(),-1))  
| summarize  
TotalGBytes =round(sum(_BilledSize/(1024*1024*1024)),2)  
by bin(TimeGenerated, 1d) //, Solution=tt  
| summarize round(avg(TotalGBytes),2), min(TimeGenerated), max(TimeGenerated)  // remove the min and max when you are happy  

or using the Usage table (which is aggregated, so the query is much faster)

//let now_ = datetime("01/01/2023, 12:00:00.000 AM");   // use to test if Jan returns December date   
let now_ = now();  
Usage  
| where TimeGenerated between (startofmonth(now_,-1) .. endofmonth(now_,-1))  
| summarize  
        TotalGBytes =round(sum(Quantity/1000),2)  
        by bin(TimeGenerated, 1d)   
| summarize round(avg(TotalGBytes),2), min(TimeGenerated), max(TimeGenerated)