Daily Data Usage And User Analysis
Query
//This query compares daily data usage with unique users over 14 days
//Calculates total data usage and average consumption per user
let UsageData = Usage
| where TimeGenerated >= ago(14d)
| where IsBillable == true
| extend DayBoundary = bin(TimeGenerated - totimespan(3h), 1d) + totimespan(3h)
| summarize TotalQuantityInGB = sum(Quantity / 1000) by DayBoundary
| order by DayBoundary asc;
let SigninData = SigninLogs
| where TimeGenerated >= ago(14d)
| extend DayBoundary = bin(TimeGenerated - totimespan(3h), 1d) + totimespan(3h)
| summarize TotalUniqueUsers = dcount(UserPrincipalName) by DayBoundary
| order by DayBoundary asc;
UsageData
| join kind=inner (SigninData) on DayBoundary
| extend AverageGBPerUser = round(TotalQuantityInGB / TotalUniqueUsers, 4)
| extend AverageMBPerUser = round((TotalQuantityInGB * 1000) / TotalUniqueUsers, 0)
| project DayBoundary, TotalQuantityInGB, TotalUniqueUsers, AverageGBPerUser, AverageMBPerUser
| order by DayBoundary ascExplanation
This query analyzes data usage and user activity over the past 14 days. Here's a simplified breakdown:
-
Data Collection:
- It gathers data usage records from the
Usagetable and user sign-in records from theSigninLogstable, both filtered to include only the last 14 days.
- It gathers data usage records from the
-
Data Processing:
- For data usage:
- It filters for billable usage entries.
- It calculates the total data used each day (in gigabytes), adjusting for a 3-hour offset to align with a specific daily boundary.
- For user sign-ins:
- It counts the number of unique users signing in each day, also adjusting for the 3-hour offset.
- For data usage:
-
Data Combination:
- It combines the daily data usage and unique user counts by matching them on the same day.
-
Calculations:
- It calculates the average data consumption per user for each day, both in gigabytes and megabytes.
-
Output:
- The final result shows each day's boundary, total data used, total unique users, and the average data used per user in both gigabytes and megabytes, sorted by date.
Details
Muzammil Mahmood
Released: November 10, 2024
Tables
UsageSigninLogs
Keywords
UsageSigninLogsUsers
Operators
let|where>=ago==trueextend-totimespanbin+summarizesum/order byascjoin kind=inneronround*project