KQL Search

Query Details

HomeAI ToolsDevice Query

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.

Details

Muzammil Mahmood profile picture

Muzammil Mahmood

Released: November 10, 2024

Tables

UsageSigninLogs

Keywords

UsageSigninLogsUsers

Operators

let|where>=ago==trueextend-totimespanbin+summarizesum/order byascjoin kind=inneronround*project

Actions