3 KQL templates that can be used with Microsoft Sentinel! Tips for speeding up incident investigations

 

"I got an alert, but I don't know where to start."
"I can write KQL, but it's hard to write from scratch every time."
"It takes too much time during the investigation, and the initial action is delayed."

KQL (Kusto Query Language) is a language for examining logs for Microsoft Sentinel and Defender products.
If you can use it well, the initial speed will change dramatically.
This time, we will introduce the "three templates to hit first" that are really used in the field.

1. Why Templates Are Important

Incident response is a race against time.
If you think about "which table to look at" and "which items to filter" every time, the important initial action time will melt.

If you prepare the template in advance:

• Reduced survey start time
• Reduced missed surveys
• Even junior members can conduct a certain level of research.

2. Template (1): Investigation from the Sign-in Point

It is a template that develops the actions before and after based on suspicious sign-ins.

kql

Target user's most recent sign-in history
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == "target@example.com"
| project TimeGenerated, UserPrincipalName, IPAddress, Location, AppDisplayName,
          ConditionalAccessStatus, RiskLevelDuringSignIn, ResultType
| order by TimeGenerated desc

Here are some points to check:

• IPAddress: Is it accessed from a different country/region than usual?
• RiskLevelDuringSignIn: Is Microsoft's AI determining risk?
• ConditionalAccessStatus: Are you bypassing conditional access?
• ResultType: Success (0) or failure (50126, etc.)?

If you find a suspicious sign-in, check for attempts from that IP to other accounts as well.

kql

Sign-in attempts to multiple users from the same IP
SigninLogs
| where TimeGenerated > ago(24h)
| where IPAddress == "suspicious.ip.here"
| summarize count(), make_set(UserPrincipalName) by IPAddress

3. Template (2): Investigation from the Terminal Point

When suspicious behavior is detected on an endpoint, it is a template that captures the overall picture of the terminal.

kql

Target device's most recent process launch history
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where DeviceName == "target-device-name"
| project TimeGenerated, DeviceName, InitiatingProcessFileName,
          FileName, ProcessCommandLine, AccountName
| order by TimeGenerated desc

Additionally, check your network connection.

kql

External communication history of the target device
DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| where DeviceName == "target-device-name"
| where RemoteIPType != "Private"
| project TimeGenerated, DeviceName, RemoteIP, RemoteUrl, RemotePort,
          InitiatingProcessFileName, ActionType
| order by TimeGenerated desc

Confirmation points:

• Suspicious command execution in ProcessCommandLine:p owershell, cmd, wscript, etc.
• RemoteUrl: Communicate to known C2 domains and suspicious IPs
• InitiatingProcessFileName: Executable file disguised as a legitimate process

4. Template (3): Email-Starting Investigation

This template is used to investigate phishing emails and malicious attachments.

kql

Email receipt history from a specific domain
EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromDomain == "suspicious-domain.com"
| project TimeGenerated, SenderFromAddress, RecipientEmailAddress,
          Subject, ThreatTypes, DeliveryAction, DeliveryLocation
| order by TimeGenerated desc

It also looks to see if the user who received the email opened the attachment.

kql

Device activity after receiving an email (tracking after clicking)
EmailUrlInfo
| where TimeGenerated > ago(7d)
| join kind=inner (
    DeviceProcessEvents
    | where TimeGenerated > ago(7d)
) on $left. Url == $right. ProcessCommandLine
| project TimeGenerated, Url, DeviceName, FileName, AccountName

5. How to Share and Manage Templates with Your Team

It is realistic to manage KQL templates as "Saved Queries" in Sentinel or as Markdown files in Git repositories.

Recommended management methods:

• Create a KQL query collection repository on GitHub
• Divide folders by incident type (phishing/, account-compromise/, etc.)
• Leave a comment on whether it was effective after use and continue to improve it.

6. Colorkrew Security's Approach

Colorkrew Security provides support for the development of KQL survey templates and their establishment in the team.

The **speed of the initial response determines the magnitude of the damage. **
From template maintenance to standardization of survey procedures, we will make it usable in the field.