KQL for First-Line Security Investigations in Microsoft Sentinel

Kusto Query Language (KQL) lets an analyst move from a broad event set to a small, explainable set of records. This guide uses Microsoft Sentinel’s Logs experience and the SigninLogs table to practice time scoping, projection, filtering, sorting, and aggregation.

The examples are read-only. They assume Entra sign-in data is connected to the workspace; tables and columns depend on enabled data sources and your tenant configuration.

Figure 1: Start with a bounded query, inspect representative rows, then add conditions that answer a specific question.

---

Step 1: Confirm the Workspace and Data Source

01

Open the Correct Sentinel Workspace

Query Scope

Open the intended Microsoft Sentinel workspace and its Logs view. Confirm the workspace name, selected time range, and that the Microsoft Entra sign-in connector or equivalent export is configured. Sentinel usage and retention can affect cost, so use an approved workspace and follow your organization’s data-handling rules.

Workspace: la-soc-lab
Data needed: Entra sign-in logs
Initial range: Last 24 hours (change to match the case timeline)

❯ View Expected Console Output

The workspace is in scope and the analyst has query permission.

No rows do not prove no activity

The table may be absent or empty because the connector is disabled, ingestion is delayed, the selected workspace is wrong, the user lacks permissions, or the event is outside retention. Check data collection and the query time range before closing an investigation.

---

Step 2: Inspect the Table Before Filtering

02

Check What the Workspace Actually Contains

Schema Check

Run a small query first. Confirm recent records exist and inspect their field names before relying on a copied query. For ingestion-delay analysis, compare TimeGenerated (workspace ingestion time) with CreatedDateTime (sign-in event time) when the latter is present.

SigninLogs
| where TimeGenerated >= ago(24h)
| take 5

❯ View Expected Console Output

Confirm: UserPrincipalName, AppDisplayName, IPAddress,
ResultType, ResultDescription, CorrelationId, and timestamps

---

Step 3: Build a Readable Event View

03

Project the Fields You Need

First Pass

Filter by a bounded time range, select only useful columns, sort newest first, and cap the first result set. A small projection is easier to review and safer to export than every field in the source record.

SigninLogs
| where TimeGenerated >= ago(24h)
| project TimeGenerated, CreatedDateTime, UserPrincipalName,
          AppDisplayName, IPAddress, ResultType,
          ResultDescription, ConditionalAccessStatus, CorrelationId
| sort by TimeGenerated desc
| take 100

❯ View Expected Console Output

Review rows in context; preserve the workspace and query time range
with any exported evidence.

---

Step 4: Focus on Failures and Summarize Carefully

04

Find Repeated Failures by Source and Hour

Triage Pivot

In the Entra sign-in schema, a successful result commonly has ResultType == “0”. This query groups other results by IP address and hour to make repeated failures easier to spot. It is a prioritization view, not a brute-force verdict: shared egress, password mistakes, service accounts, and legacy applications can produce clusters.

SigninLogs
| where TimeGenerated >= ago(24h)
| where ResultType != "0"
| summarize Attempts=count(),
            Users=dcount(UserPrincipalName),
            Apps=dcount(AppDisplayName)
  by IPAddress, bin(TimeGenerated, 1h)
| sort by Attempts desc

❯ View Expected Console Output

Candidate: 198.51.100.42  |  8 failures  |  3 users  |  2 apps
Next: inspect the raw records, identity context, client, and time pattern.

---

Step 5: Pivot to One User or Correlation ID

05

Narrow the Search to a Case Entity

Correlation

Replace the sample UPN with the exact case identity, or use a correlation ID from a known event. Compare the user’s expected location, application, device, authentication result, and adjacent sign-ins. Use the event timestamp as well as the workspace ingestion timestamp when building a timeline.

let targetUser = "[email protected]";
SigninLogs
| where TimeGenerated between (ago(24h) .. now())
| where UserPrincipalName =~ targetUser
| project TimeGenerated, CreatedDateTime, UserPrincipalName,
          AppDisplayName, IPAddress, ResultType, ResultDescription,
          DeviceDetail, ConditionalAccessStatus, CorrelationId
| sort by TimeGenerated asc

❯ View Expected Console Output

Use the query result to identify records to open and correlate;
do not infer user intent from a location or IP alone.

---

Step 6: Save the Investigation Context

06

Make the Query Repeatable

Case Notes

Save the query with a clear name, replace broad time ranges with the incident window when appropriate, and record the workspace, table, time zone, query run time, and any missing fields. If the result suggests an incident, follow the existing incident playbook and pivot into the relevant identity or endpoint investigation.

Question answered:
Workspace and table:
Query time range and time zone:
Key event IDs / correlation IDs:
Observed facts:
Interpretation and confidence:
Next owner and action:

❯ View Expected Console Output

Conclusion is traceable to a bounded query and specific source records.

References

Microsoft Sentinel data connectors and tables · KQL common tasks for Microsoft Sentinel · Example queries for the SigninLogs table · Microsoft Sentinel billing and cost management