7.1 make_series. Converting Events into Time-Series Data

Every security log table stores discrete events, individual rows representing something that happened at a specific time. A sign-in occurred. A process launched. A file was accessed. These events are powerful for investigation ("what happened at 14:22?") but inadequate for behavioral analysis ("is this user's activity pattern normal?").

Behavioral analysis requires time-series data: measurements taken at regular intervals over a continuous period. "How many sign-ins did this user generate per hour for the last 30 days?" is a time series. It has a consistent interval (1 hour), a continuous timespan (30 days), and a scalar value at each point (count of sign-ins). This structure enables statistical operations that are impossible on discrete events: trend detection, seasonal decomposition, anomaly scoring, and forecasting.

The make_series operator converts discrete event rows into time-series arrays. It is the bridge between log data and statistical analysis.

Basic make_series syntax

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| make-series SignInCount = count() default=0
    on TimeGenerated
    from ago(7d) to now()
    step 1h
    by UserPrincipalName

This produces one row per user. Each row contains:

• UserPrincipalName: the series identifier
• SignInCount: a dynamic array of 168 values (7 days × 24 hours), each value being the count of successful sign-ins in that 1-hour bin
• TimeGenerated: a dynamic array of 168 datetime values, each being the start of a 1-hour bin

The default=0 parameter is critical. Without it, hours with no sign-in events are missing from the array, creating irregular gaps that break downstream statistical functions. With default=0, every hour has a value, either the actual count or zero, producing a regular time series.

Understanding the output structure

The output of make_series looks unusual because the values are stored as arrays, not individual rows:

UserPrincipalName    | SignInCount                    | TimeGenerated
j.morrison@north...  | [0,0,0,3,5,12,8,4,2,0,0,...]  | [2026-03-17T00:00Z, 2026-03-17T01:00Z, ...]
s.chen@north...      | [0,0,0,1,2,7,5,3,1,0,0,...]   | [2026-03-17T00:00Z, 2026-03-17T01:00Z, ...]

Each array element corresponds to one time bin. Index 0 is the first bin (7 days ago, midnight UTC). Index 167 is the last bin (the current hour). The TimeGenerated array provides the timestamp label for each index.

To extract a specific value from the array, use bracket indexing:

| extend CurrentHourSignIns = SignInCount[array_length(SignInCount) - 1]
| extend YesterdaySameHour = SignInCount[array_length(SignInCount) - 25]

Choosing the right step size

The step parameter determines the granularity of the time series. The choice depends on the detection use case:

1-minute step: Detects burst activity, brute force attacks, DDoS, automated tooling. Produces 10,080 data points per week. Use for short lookback windows (1-4 hours) only, longer windows generate arrays too large for efficient processing.

5-minute step: Balances granularity with performance for short-term monitoring. Good for detecting credential spraying (steady 5-minute cadence) and data exfiltration bursts.

1-hour step: The standard step for most security time-series analysis. Captures daily patterns (business hours vs off-hours) without excessive data points. 168 points per week. Use for 7-30 day lookback windows.

1-day step: Captures weekly patterns (weekday vs weekend). 30 points per month. Use for long-term baseline comparison (30-90 day lookbacks).

Rule of thumb: The step should be small enough to capture the anomaly you want to detect but large enough to keep the array under 1,000 data points. Anomaly detection functions (covered in subsection 7.3) degrade with very long arrays.

Multiple aggregations in one make_series

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| make-series 
    SignInCount = count() default=0,
    UniqueIPs = dcount(IPAddress) default=0,
    UniqueApps = dcount(AppDisplayName) default=0
    on TimeGenerated
    from ago(30d) to now()
    step 1d
    by UserPrincipalName

This produces three parallel time-series arrays per user: sign-in count, distinct IP count, and distinct application count: all aligned to the same daily bins. Comparing these three series reveals different types of anomalies:

• Sign-in count spike alone → increased activity (possibly legitimate, e.g., project deadline)
• Unique IP spike alone → account accessed from new locations (possible credential compromise)
• All three spike simultaneously → account takeover with broad access from new infrastructure

The from-to window and alignment

The from and to parameters define the exact boundaries of the time series:

| make-series count() default=0
    on TimeGenerated
    from datetime(2026-03-01T00:00:00Z) to datetime(2026-03-31T23:59:59Z)
    step 1d

This produces exactly 31 data points, one per day in March 2026. Without explicit from and to, the series starts at the earliest event and ends at the latest, which can produce inconsistent array lengths when comparing entities with different activity patterns.

Best practice for detection rules: Always use explicit from ago(Xd) to now() to ensure consistent array lengths across all entities. Inconsistent array lengths cause series_decompose_anomalies to fail or produce misleading results.

Series statistics, extracting summary metrics from arrays

Before diving into decomposition (subsection 7.2), you can extract useful statistics from raw time-series arrays using series_stats_dynamic:

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| make-series SignInCount = count() default=0
    on TimeGenerated from ago(30d) to now() step 1h
    by UserPrincipalName
| extend Stats = series_stats_dynamic(SignInCount)
| extend 
    AvgHourly = round(toreal(Stats.avg), 1),
    MaxHourly = tolong(Stats.max),
    MinHourly = tolong(Stats.min),
    StdDev = round(toreal(Stats.stdev), 2),
    Variance = round(toreal(Stats.variance), 2)
| project UserPrincipalName, AvgHourly, MaxHourly, MinHourly, StdDev, Variance
| sort by StdDev desc
| take 20

series_stats_dynamic returns a property bag containing: min, max, avg, stdev, variance, and count. Sorting by StdDev desc surfaces the most volatile users, accounts whose hourly sign-in volume varies the most. High variance accounts are either erratic users (switching between heavy and light work periods) or compromised accounts exhibiting burst activity.

Quick anomaly detection without decomposition: The simplest anomaly check is whether the most recent value exceeds the mean plus N standard deviations:

| extend LatestValue = toreal(SignInCount[array_length(SignInCount) - 1])
| extend UpperBound = AvgHourly + (3 * StdDev)
| where LatestValue > UpperBound
| project UserPrincipalName, LatestValue, AvgHourly, UpperBound, StdDev

This is crude compared to series_decompose_anomalies (it does not account for seasonal patterns), but it runs faster and is useful for quick scoping during an incident: "which accounts are currently behaving far outside their normal range?"

Series arithmetic, comparing two time series

You can perform element-wise arithmetic on two arrays of the same length:

SigninLogs
| where TimeGenerated > ago(14d)
| where ResultType == "0"
| make-series 
    SuccessCount = countif(ResultType == "0") default=0,
    TotalCount = count() default=0
    on TimeGenerated from ago(14d) to now() step 1h
    by UserPrincipalName
| extend FailCount = series_subtract(TotalCount, SuccessCount)
| extend FailRate = series_divide(FailCount, TotalCount)

series_subtract, series_add, series_multiply, and series_divide operate element-by-element. The FailRate array now contains the failure rate for each hour: a value between 0.0 (all successes) and 1.0 (all failures). A sudden spike in the failure rate array indicates a credential attack against that account, even if the absolute failure count is low.

Handling sparse data, when most bins are zero

Some entities have very low activity: a service account that authenticates once per day, a VPN user who connects twice per week. Their time series is mostly zeros with occasional values. This sparse data causes problems for decomposition and anomaly detection because there is not enough signal to establish a seasonal pattern.

Detection: Count the percentage of zero bins:

| extend ZeroPct = round(100.0 * countof(tostring(SignInCount), "0") / array_length(SignInCount), 1)
| where ZeroPct < 80  // Only analyze entities with >20% non-zero bins

Recommendation: For entities with more than 80% zero bins, use simpler detection methods (first-seen patterns, threshold-based rules) rather than time-series decomposition. The statistical methods in subsections 7.2-7.3 need a minimum density of non-zero values to produce meaningful results.

make_series with multiple grouping keys

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| make-series FailCount = count() default=0
    on TimeGenerated from ago(7d) to now() step 1h
    by IPAddress, tostring(LocationDetails.countryOrRegion)

This creates one time series per IP-country combination. An IP that generates failures from two different countries (impossible travel for an IP, likely a proxy or VPN) produces two separate series, each showing the failure pattern from that country. Comparing them reveals whether the failures are from a single source or distributed infrastructure.

Create a time series of failed sign-in counts per IP address, binned hourly over the last 7 days. Which IPs show a sudden spike in failures? Use | extend MaxHourly = array_max(FailCount) to find the peak value for each IP, then filter to IPs where the peak exceeds 50 failures in a single hour.

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| make-series FailCount = count() default=0
    on TimeGenerated
    from ago(7d) to now()
    step 1h
    by IPAddress
| extend MaxHourly = toint(series_stats_dynamic(FailCount).max)
| where MaxHourly > 50
| project IPAddress, MaxHourly, FailCount
| sort by MaxHourly desc

NE environmental considerations

NE's detection environment includes specific factors that influence this rule's operation:

Device diversity: 768 P2 corporate workstations with full Defender for Endpoint telemetry, 58 P1 manufacturing workstations with basic cloud-delivered protection, and 3 RHEL rendering servers with Syslog-only coverage. Rules targeting DeviceProcessEvents operate with full fidelity on P2 devices but may have reduced visibility on P1 devices. Manufacturing workstations in Sheffield and Sunderland represent a detection gap for endpoint-level detections.

Network topology: 11 offices connected via Palo Alto SD-WAN with full-mesh connectivity. The SD-WAN firewall logs feed CommonSecurityLog in Sentinel. Cross-site lateral movement generates firewall allow events that correlate with DeviceLogonEvents, enabling multi-source detection that single-table rules cannot achieve.

User population: 810 users with distinct behavioral profiles, office workers (predictable hours, consistent applications), field engineers (variable hours, travel patterns), IT administrators (elevated privilege, broad access patterns), and manufacturing operators (fixed shifts, limited application access). Each user population has different detection baselines.

Troubleshooting

"The query returns an error I do not understand." KQL error messages reference the specific line and operator that failed. Read the error message from left to right: it names the operator, the expected input type, and the actual input type. Most errors are type mismatches (passing a string where a datetime is expected) or field name typos. The getschema operator shows every field name and type for any table: TableName | getschema.

"The query runs but returns unexpected results." Add | take 10 after each operator in the pipeline and examine the intermediate output. This reveals WHERE the data transforms in a way you did not expect. Debug the pipeline stage by stage, not the entire query at once.

Interactive lab: tune the time-series anomaly threshold

Adjust the file count threshold, time window, and crown jewel filter for the SharePoint bulk download detection. This lab uses the same parameters as DE7-002, demonstrating how time-series analysis translates directly into detection rules.