KQL Search

Query Details

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 asc

Explanation

This query analyzes data usage and user activity over the past 14 days. Here's a simplified breakdown:

  1. Data Collection:

    • It gathers data usage records from the Usage table and user sign-in records from the SigninLogs table, both filtered to include only the last 14 days.

  2. 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.

  3. Data Combination:

    • It combines the daily data usage and unique user counts by matching them on the same day.

  4. Calculations:

    • It calculates the average data consumption per user for each day, both in gigabytes and megabytes.

  5. 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.