Summarizing Logs

Threat hunting in Microsoft Sentinel often requires distilling large volumes of log data into actionable insights. Summarizing and rendering log data using KQL (Kusto Query Language) is critical for identifying patterns, anomalies, and trends. This section covers core techniques for aggregating and presenting data effectively.

---

Summarize Operator for Aggregation

The summarize operator is central to reducing raw logs into structured summaries. It allows you to compute metrics like counts, averages, or totals while grouping data by specific fields.

Basic Syntax

YourTable
| summarize [aggregation], [aggregation], ...

Example: Counting Events by Source IP

SecurityEvent
| where EventID == 4624
| summarize Count = count() by SourceIP

This query counts successful logon events (EventID 4624) grouped by the source IP address, helping identify potential brute-force attempts.

Advanced Aggregations

Combine multiple aggregations for richer insights:

SecurityEvent
| where EventID in (4624, 4625)
| summarize 
    SuccessCount = countif(EventID == 4624), 
    FailureCount = countif(EventID == 4625), 
    AvgDuration = avg(Duration) 
    by SourceIP

This example differentiates between successful and failed logons and calculates average durations.

---

Rendering Results with format

The format operator transforms query results into structured output (e.g., tables, lists) for easier analysis.

Common Templates

• table: Default tabular format.
• list: Simple list of values.
• markdown: Human-readable markdown output.

Example: Formatting as Markdown

SecurityEvent
| where EventID == 4624
| summarize Count = count() by SourceIP
| format markdown

This produces a markdown table, ideal for sharing findings in reports or chat tools.

Customizing Output

Use parameters to control formatting:

SecurityEvent
| summarize Count = count() by SourceIP
| format table (columns: SourceIP, Count, "MaxTime": max(TimeGenerated))

This specifies columns and adds a calculated field (MaxTime).

---

Grouping and Aggregation for Context

Grouping by multiple fields and combining aggregations provides deeper context. For example, analyzing user activity alongside IP addresses:

SecurityEvent
| where EventID == 4624
| summarize 
    Count = count(), 
    LastLogin = max(TimeGenerated) 
    by User, SourceIP

This groups results by both user and IP, helping identify if a single user is associated with multiple suspicious IPs.

---

Advanced Techniques

1. Conditional Summarization: Use where clauses to filter data before aggregation.
   

   SecurityEvent
   | where EventID == 4624 and SourceIP in ("192.168.1.1", "10.0.0.1")
   | summarize Count = count() by SourceIP
   

2. Time-Based Grouping: Use bin to aggregate data over time intervals.
   

   SecurityEvent
   | summarize Count = count() by bin(TimeGenerated, 1h)
   

3. Combining with extend: Add calculated fields for enhanced analysis.
   

   SecurityEvent
   | extend RiskScore = if(EventID == 4624, 10, 0)
   | summarize TotalRisk = sum(RiskScore) by SourceIP
   

---

Key takeaways

• Use summarize to compute metrics and group data by relevant fields.
• Render results with format to create structured outputs for reporting.
• Group by multiple fields to uncover relationships between entities (e.g., user + IP).
• Leverage advanced techniques like time-based grouping and conditional filters for nuanced analysis.
• Prioritize clarity and context when designing summaries to support rapid decision-making.