Sentinel Billable data

Question

Hello can you please help me understand difference of two queries we received from vendor deployin sentinel.

We have logic app running daily this query to see billable data (to monitor if we are reaching cap).

Usage
| where TimeGenerated > ago(1d)
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(TimeGenerated, 31d), Solution
| summarize TotalDataGB = sum(BillableDataGB)

Also we got the visualisation in chart over mont

Usage
|where TimeGenerated > ago (30d)
|where IsBillable == true
| summarize BillableDataGB=sum(Quantity) / 1000. by bin(TimeGenerated, 1d), Solution
| render columnchart

However often there is big difference, while the first one reports over several days numbers 300-400, when i look at the data in second I see peaks to 700 GB.

Example below. On 22June we see peak to 700GB, however the outcome of the first query was always 300-400 GB when reported

23.6. reported previous daily ingestion : 415.907715810097 GB.

22.6 reported previous daily ingestion : 367.10762928873 GB.

Does not make sense to me have such big difference.

ALSO WHAt Query for monnitoring and analyzing daily ingestion are you using please???

clive_watson · Answer

T150732D&nbsp;&nbsp;For query one, you only need this KQL for the same result.&nbsp;Usage
| where TimeGenerated &gt; ago(1d)
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000.However you may be better off adding a time alignment, as you dont say when the Playbooks runs, and you will get different results depending on the time of day, however if you add startofday() you always get from the first record after midnight.&nbsp; See here for more&nbsp;How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - Microsoft Tech Community &nbsp;Usage
| where TimeGenerated &gt; startofday(ago(1d))
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000.&nbsp;

clive_watson · Answer

T150732D&nbsp;&nbsp;Example for previous 3 months (excludes current month) but you can tweak that, alter line 4&nbsp; from "-1" to "-0"let price_ = 4.0;  // enter you price per GB for LogAnalytics + Sentinel 
Usage
// get logs for previous 3 months
| where TimeGenerated between(startofmonth(now(),-3) ..endofmonth(endofmonth(now(),-1)) )
| summarize GBytesTotal=sum(Quantity) / 1000, Price=sum(Quantity) / 1000 * price_ by month=bin(datepart("Month", TimeGenerated), 1)
// format month so we can sort it
| extend month = case(month==1,strcat(month,'. Jan'),month==2,strcat(month,'. Feb'),month==3, strcat(month,'. Mar'),month==4,strcat(month,'. Apr'),month==5,strcat(month,'. May'),month==6,strcat(month,'. Jun'),month==7, strcat(month,'. Jul'),month==8,strcat(month,'. Aug'),month==9,strcat(month,'. Sep'),month==10,strcat(month,'. Oct'),month==11,strcat(month,'. Nov'),month==12,strcat(month,'. Dec'),"error")
| order by month asc&nbsp;&nbsp;You'll see this in the next version of the "Workspace Usage Report" workbook, in a week or so I hope.&nbsp;

clive_watson · Answer

btw, there are two articles/Playbooks from Microsoft:

https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/ingestion-cost-spike-detection-playbook/ba-p/2591301

and

https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/ingestion-cost-alert-playbook/ba-p/2006003

mikhailf · Answer

Hello&nbsp;T150732D&nbsp;,&nbsp;Instead of| where TimeGenerated &gt; ago(1d)I would use:| where StartTime &gt;= startofday(ago(1d)) and EndTime &lt; startofday(now())&nbsp;I tried to use the queries you sent and got the same results. Will try to check it deeper and update you later.

clive_watson · Answer

I tend to prefer "between" for this...but either work| where TimeGenerated between ( startofday(ago(1d)) .. endofday(ago(1d)) )

Forum Discussion

Sentinel Billable data

Share

Resources