KQL Search

Query Details

Log Analytics Workspace Daily Ingestion

Query

//This query calculates daily ingested data with moving average in Log Analytics workspace
//Shows sum of billable data per day and moving average trend using series_fir()
//configure lookback period
let lookback = 365d;
Usage
//iclude only billable data and remove current day in time filter
| where TimeGenerated between(ago(lookback)..now(-1d)) and IsBillable == true
| project TimeGenerated, Quantity
//create series representing data ingested per day (converted from MB to GB)
| make-series DailyIngestionGB=sum(Quantity/1000) default=0 on TimeGenerated step 1d
//calculate moving average with FIR function using fixed size filter (5) of equal coefficients
| extend MovingAvg = series_fir(DailyIngestionGB,repeat(1, 5))
| project TimeGenerated, DailyIngestionGB, MovingAvg
//render on a line graph
| render timechart with (xtitle = "Date", ytitle = "Ingested data (GB)")

Explanation

This query is designed to analyze and visualize the daily amount of billable data ingested into a Log Analytics workspace over the past year, excluding the current day. Here's a simplified breakdown of what the query does:

  1. Lookback Period: It sets a lookback period of 365 days to examine data from the past year.

  2. Filter Data: It filters the data to include only the billable data entries and excludes the current day's data.

  3. Project Relevant Fields: It selects the TimeGenerated and Quantity fields for further processing.

  4. Daily Data Series: It creates a daily series of data ingestion, converting the quantity from megabytes (MB) to gigabytes (GB).

  5. Moving Average Calculation: It calculates a moving average of the daily ingested data using a Finite Impulse Response (FIR) filter with a fixed size of 5, which smooths out short-term fluctuations and highlights longer-term trends.

  6. Visualization: Finally, it renders the results on a line graph, with the x-axis representing the date and the y-axis representing the amount of ingested data in gigabytes.

This visualization helps in understanding the trend of data ingestion over time, making it easier to spot patterns or anomalies.