Sentinel Cost Workbook

[DGMalcolm]

I've added the "Sentinel Cost Workbook" and I've found it gives me a lot of valuable information. However, near the top, under the section entitled "Breakdown of billable ingestion by log category in the last > ago(7d)" the largest category is "Other". That category represents over 75% of the data ingestion. Is there a way to get more detail for what that data is?

TIA

~DGM~

[#2 · Accepted solution]

1. Workspace Usage - workbook shows all tables
2. Or if you are happy doing an edit to the Workbook, replace this section of KQL in that grid with this change (I haven't done a copy & paste of the full syntax) :

let customTables = Usage
| where IsBillable == true
| where DataType contains "_CL"
| summarize size = sum(Quantity)/1000 by DataType
| project ['Log Type'] = DataType, ['Table Size'] = size, ['Estimated cost'] = size*{Price};
let knownTables = Usage
| where IsBillable == true
| join kind=leftouter Categories on $left.DataType == $right.Type
| summarize size =sumif(Quantity, isnotempty(Category))/1000, sizeOther= sumif(Quantity,(isempty(Category) and DataType !contains "_CL"))/1000 by Category, Type
| project ['Log Type'] = iif(isnotempty( Category),Category,strcat("Other: ",Type)), ['Table Size'] = iif(isnotempty( Category),size,sizeOther), ['Estimated cost'] = iif(isnotempty(Category),size*{Price},sizeOther*4);
union customTables, knownTables
| order by ['Table Size'] desc

[#3]

Warning Over the top explanation, scroll to the bottom for the answer

Just to give a break down what the workbook does:

The section in the workbook you are on about basically creates a table assigning the Data types logs into the correct naming solutions, for example a snippet from the code:

"Datatype Logs" Are apart of the solution "AAD", and then you get a costing for the solution AAD rather than individual logs.

"AuditLogs", "Azure Active Directory",
"SigninLogs", "Azure Active Directory",
"AADNonInteractiveUserSignInLogs", "Azure Active Directory",
"AADServicePrincipalSignInLogs", "Azure Active Directory",
"AADManagedIdentitySignInLogs", "Azure Active Directory",
"AADProvisioningLogs","Azure Active Directory",

Then it Joins this and any Custom Logs you are ingesting (| where DataType contains "_CL")

Anything labelled as other is because again, snipping the relevant code, basically saying if the result is empty in Log Type, mark it as "Other"

| project ['Log Type'] = DataType
| project ['Log Type'] = iif(isnotempty( Category),Category,"Other")

So any logs that arnt captured under the categories at the start / anything that does not have CL will be marked as Other.

Looking at a table that is flagged as Other in my environment "ContainerLog" is captured so is flagged as Other

Answer: 

Long story Short This should give you a good view of everything cost wise:

Usage
| where TimeGenerated > ago(7d)
| where StartTime >= startofday(ago(7d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000. by Solution, DataType
| order by BillableDataGB desc

Other Documents to assist

https://docs.microsoft.com/en-gb/azure/azure-monitor/logs/manage-cost-storage#troubleshooting-why-us...

[#4]

@Clive_Watson 

Thank you for this. It seemed like the top table was probably responsible for Other but I wasn't sure. This helped a lot.

Thanks

~DGM~

[#5]

Thank you, this query is a good help and a starting point. So much to learn.

Thanks
~DGM~