Query Details
Savingperworkbook
Query
//For the Cost workbook. Shows E5 savings per workspace
let DailyMaxDiscountGB = ((5*toreal({TotalE5Seats}))/1024);
Usage
| where IsBillable == true
| where DataType in ("SigninLogs",
"AuditLogs",
"AADNonInteractiveUserSignInLogs",
"AADServicePrincipalSignInLogs",
"AADManagedIdentitySignInLogs",
"AADProvisioningLogs",
"ADFSSignInLogs",
"McasShadowItReporting",
"InformationProtectionLogs_CL",
"DeviceEvents",
"DeviceFileEvents",
"DeviceImageLoadEvents",
"DeviceInfo",
"DeviceLogonEvents",
"DeviceNetworkEvents",
"DeviceNetworkInfo",
"DeviceProcessEvents",
"DeviceRegistryEvents",
"DeviceFileCertificateInfo",
"EmailAttachmentInfo",
"EmailEvents",
"EmailPostDeliveryEvents",
"EmailUrlInfo",
"IdentityLogonEvents",
"IdentityQueryEvents",
"IdentityDirectoryEvents",
"AlertEvidence",
"CloudAppEvents")
| extend workspaceName = tostring(split(ResourceUri, "/")[-1])
| extend subscription = tostring(split(ResourceUri, "/")[2])
| extend resourceGroup = tostring(split(ResourceUri, "/")[4])
| summarize DailyBillableGB = toreal(sum(Quantity))/ 1024 by format_datetime(TimeGenerated, 'yy-MM-dd'), workspaceName
| summarize TotalEligibleGB = sum(iif(toreal(DailyBillableGB)>toreal(DailyMaxDiscountGB),toreal(DailyMaxDiscountGB),DailyBillableGB)) by workspaceName
| extend TotalDiscount = toreal(TotalEligibleGB)*{Price}
| project workspaceName, TotalDiscount
Explanation
This query is used to calculate the total discount for each workspace in the Cost workbook based on the E5 savings. It filters the usage data for billable items and specific data types. It then calculates the daily billable usage in GB for each workspace and determines the maximum discount allowed based on the total E5 seats. The query summarizes the eligible usage and calculates the total discount for each workspace. The final result includes the workspace name and the total discount amount.
Details
Rod Trent
Released: September 20, 2023
Tables
Usage
Keywords
Devices,Intune,User
Operators
whereinextendsummarizeiiftorealsplitformat_datetimesumproject