Usage Cloud App Events
Query
CloudAppEvents
| where TimeGenerated > ago(90d)
| where _IsBillable == true
| summarize TotalVolumeGBLog = round(sum(_BilledSize/1024/1024/1024),2) by bin(TimeGenerated, 1d)
// Sum all
| summarize sum(TotalVolumeGBLog)
```kql
Total ingestion in GB into the CloudAppEvents table, broken down by application that is connected via
the App Connector in Defender for Cloud Apps
```kql
CloudAppEvents
| where TimeGenerated > ago(90d)
| where _IsBillable == true
| summarize TotalVolumeGBLog = round(sum(_BilledSize/1024/1024/1024),2) by bin(TimeGenerated, 1d), Application
// Sum all
| summarize round(sum(TotalVolumeGBLog),2) by Application
| render columnchart
```kql
// here we're comparing the Ingested GB between the CloudAppEvents and the Usage table.
// You might have to adjust the ingestionprice for Sentinel/LogAnalytics.
```kql
let ingestionprice = 5.59;
let logsource = "CloudAppEvents";
let xusage = Usage
| where TimeGenerated > ago (30d)
| where IsBillable == true
| summarize TotalVolumeGBUsage = round(sum(Quantity/1024),2) by bin(TimeGenerated, 1d), DataType
| where DataType == (logsource);
CloudAppEvents
| where TimeGenerated > ago (30d)
| where _IsBillable == true
| summarize TotalVolumeGBLog = round(sum(_BilledSize/1024/1024/1024),2) by bin(TimeGenerated, 1d)
| join xusage
on $left.TimeGenerated == $right.TimeGenerated
| extend ['Estimated cost'] = TotalVolumeGBLog * ingestionprice
| summarize round(sum(TotalVolumeGBUsage),2), round(sum(TotalVolumeGBLog),2) , round(sum(['Estimated cost']),2)About this query
Usage - CloudAppEvents
Query Information
Description
The CloudAppEvents table in the advanced hunting schema contains information about events involving accounts and objects in Office 365 and other cloud apps and services.
Use the below queries to review the data usage within the CloudAppEvents table in Microsoft Sentinel.
References
Microsoft Sentinel
// Total ingestion in GB into the CloudAppEvents table
// show how much the CloudAppEvents data usage compares to the rest of the logs.
Usage
| where TimeGenerated > ago (30d)
| where IsBillable == true
| summarize TotalVolumeGBUsage = round(sum(Quantity/1024),2) by DataType
| summarize CloudAppEvents = sumif(TotalVolumeGBUsage, DataType == 'CloudAppEvents'), OtherLogs = round(sumif(TotalVolumeGBUsage,DataType != 'CloudAppEvents'),2)
| extend Pct = round(CloudAppEvents*100/OtherLogs,2)
Explanation
This set of KQL queries is designed to analyze data usage within the CloudAppEvents table in Microsoft Sentinel, focusing on the volume of data ingested and its associated costs. Here's a simplified summary of each query:
-
Total Data Ingestion Over 90 Days:
- This query calculates the total volume of data ingested into the CloudAppEvents table over the past 90 days. It filters for billable data and sums up the size in gigabytes, providing a daily breakdown.
-
Data Ingestion by Application:
- This query breaks down the total data ingestion by application over the past 90 days. It shows how much data each application, connected via the App Connector in Defender for Cloud Apps, contributes to the total ingestion. The results are visualized in a column chart.
-
Comparison of Ingested Data and Usage:
- This query compares the data ingested into the CloudAppEvents table with the data usage recorded in the Usage table over the past 30 days. It calculates the estimated cost of data ingestion based on a specified price per gigabyte and provides a summary of the total usage, ingestion, and estimated cost.
-
CloudAppEvents Data Usage vs. Other Logs:
- This query compares the data usage of CloudAppEvents with other logs over the past 30 days. It calculates the total volume of data for CloudAppEvents and other logs, then determines the percentage of CloudAppEvents data relative to other logs.
These queries help in understanding the data usage patterns, costs, and contributions of different applications to the overall data ingestion in Microsoft Sentinel.
Details
Alex Verboon
Released: June 24, 2024
Tables
Keywords
Operators