Sentinel Billable data

[T150732D]

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

[#2]

@T150732D 

For query one, you only need this KQL for the same result. 

Usage
| where TimeGenerated > 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.  See here for more How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - M...  

Usage
| where TimeGenerated > startofday(ago(1d))
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000.

[#3]

btw, there are two articles/Playbooks from Microsoft:

https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/ingestion-cost-spike-detection-playbo...

and

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

[#4]

Hello @T150732D ,

Instead of

| where TimeGenerated > ago(1d)

I would use:

| where StartTime >= startofday(ago(1d)) and EndTime < startofday(now())

I tried to use the queries you sent and got the same results. Will try to check it deeper and update you later.

[#5]

I tend to prefer "between" for this...but either work

| where TimeGenerated between ( startofday(ago(1d)) .. endofday(ago(1d)) )

[#6]

@Clive_Watson thanks for your helps.

this query you proposed returns now 550 GB

Usage
| where TimeGenerated > ago(1d)
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000.

the second query  returns now 840 GB

Usage
| where TimeGenerated > startofday(ago(1d))
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000.

 the query @mikhailf proposed

| where StartTime >= startofday(ago(1d)) and EndTime < startofday(now())

returns 485 GB

My initial query returns now 550 GB

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

[#7]

Hello @T150732D ,

Please, try the following queries:

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

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

It will return similar results.

[#8]

@mikhailf can you please help me with some query to show monthly sentinel ingestion log cost?

I would like to provide management total Gb but also cost in dollars.

thank you

[#9]

@T150732D 

Example for previous 3 months (excludes current month) but you can tweak that, alter line 4  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

You'll see this in the next version of the "Workspace Usage Report" workbook, in a week or so I hope. 

[#10]

@Clive_Watson wow thats great. can you help me setup right price for the parametr?

if we have 1000 GB daily cap and 500 GB commited tier, i insert value $0.99 per GB as per https://azure.microsoft.com/en-gb/pricing/details/microsoft-sentinel/

[#11]

Correct.
If you need to get the total costs, you would need to add the 500MB commitment tier value for Azure Monitor (Log Analytics) as well https://azure.microsoft.com/en-gb/pricing/details/monitor/

$0.99 + $1.94 = $2.93

https://azure.microsoft.com/en-us/pricing/calculator/