Investigation Patterns
Ready-to-use FSQL queries for common security investigations, organized by MITRE ATT&CK technique. Each pattern includes guidance on when to use it, what to look for in results, and where to pivot next.
Pattern 1: Authentication Analysis (T1110 - Brute Force)
When to use: During initial triage of a suspected compromised account, or when monitoring for credential attacks.
Find failed login attempts for a specific user:
QUERY authentication.**
WITH authentication.user.username = 'jsmith'
AND authentication.status_id = FAILURE
SINCE 24hrsExample results (trimmed — real events contain many more fields):
[
{
"user": {"username": "jsmith", "email_addr": "[email protected]", "uid": "a1b2c3..."},
"src_endpoint": {"ip": "203.0.113.42", "hostname": "WORKSTATION07"},
"time": "2026-03-30T02:14:33.000000+00:00",
"status_id": "FAILURE",
"activity_id": "LOGON",
"severity_id": "INFORMATIONAL",
"metadata": {"product": {"name": "Active Directory", "vendor_name": "Microsoft"}},
"__event": "authentication"
},
{
"user": {"username": "jsmith", "email_addr": "[email protected]", "uid": "x9y8z7..."},
"src_endpoint": {"ip": "198.51.100.7"},
"time": "2026-03-30T02:14:35.000000+00:00",
"status_id": "FAILURE",
"activity_id": "LOGON",
"severity_id": "INFORMATIONAL",
"metadata": {"product": {"name": "Okta Identity Cloud", "vendor_name": "Okta"}},
"__event": "authentication"
},
{
"user": {"username": "jsmith", "email_addr": "[email protected]", "uid": "a1b2c3..."},
"src_endpoint": {"ip": "203.0.113.42", "hostname": "WORKSTATION07"},
"time": "2026-03-30T02:14:41.000000+00:00",
"status_id": "SUCCESS",
"activity_id": "LOGON",
"severity_id": "INFORMATIONAL",
"metadata": {"product": {"name": "Active Directory", "vendor_name": "Microsoft"}},
"__event": "authentication"
}
]What to look for: A high count of failures in a short time window, especially from diverse source IPs (credential stuffing) or a single IP with rapid attempts (brute force). Check whether failures are followed by a success — that may indicate a successful compromise. In the example above, two rapid failures from different sources followed by a success from the same IP is a strong indicator.
Pivot to: Use the source IPs from results in Pattern 2 to check for lateral movement. Use Pattern 4 to see all activity from suspicious IPs.
Pattern 2: Lateral Movement Detection (T1021 - Remote Services)
When to use: After confirming a compromised host, to identify where the attacker may have moved next.
Find potential lateral movement involving a compromised host:
QUERY #network.**
WITH #network.src_endpoint.hostname = 'WORKSTATION123'
AND #network.dst_endpoint.port IN 445, 1433, 8080What to look for: Connections to SMB (445), SQL (1433), or web admin (8080) ports from the compromised host. Unusual destination hosts — especially servers the user doesn't normally access — are strong indicators. Pay attention to connection timing relative to the initial compromise.
Pivot to: For each destination host, run Pattern 1 to check for authentication anomalies. Use Pattern 11 to check for data exfiltration via SSH.
Pattern 3: Failed MFA Logins (T1621 - Multi-Factor Authentication Request Generation)
When to use: When investigating MFA fatigue attacks or validating MFA enforcement across your environment.
Search all authentication events for failed logins with MFA, such as from Okta, Auth0, or Google Workspace Login logs:
QUERY authentication.**
WITH authentication.is_mfa = TRUE AND authentication.activity_id = LOGON AND authentication.status_id = FAILUREWhat to look for: Repeated MFA failures for a single user in a short period — this is the hallmark of an MFA fatigue/push bombing attack. Also watch for failures across many users from the same source IP, which may indicate a broader campaign.
Pivot to: Check if any of the affected users subsequently had a successful login (Pattern 4 with SUCCESS). If so, the account may be compromised.
Pattern 4: Authentication from a Specific IP (T1078 - Valid Accounts)
When to use: When you have a suspicious IP from an alert, threat intel, or another investigation and want to see all authentication activity associated with it.
Search all failed or successful authentication events from a given IP address:
QUERY authentication.**
WITH authentication.status_id IN SUCCESS, FAILURE AND authentication.src_endpoint.ip = '10.100.1.16'What to look for: Multiple accounts authenticating from the same IP (account enumeration). A mix of failures followed by success (successful brute force). Logins to privileged accounts or service accounts from an unexpected IP.
Pivot to: Use Pattern 2 with the hostname associated with this IP. Use Pattern 10 to trace SSH activity from the same IP.
Pattern 5: Logins from Devices with a Specific OS (T1078 - Valid Accounts)
When to use: When your environment is standardized on a specific OS and you want to detect unauthorized device types, or when investigating anomalous device usage.
Search for logons from macOS devices. Note that os.type_id is an enumeration, so the value does not need quotes:
QUERY authentication.**
WITH authentication.activity_id = LOGON AND authentication.device.os.type_id = MACOSIf you are unsure which enum values are valid for a field, see Data Model Primer.
What to look for: Logins from OS types that don't match your corporate fleet (e.g., Linux logins in a Windows-only environment). Correlate with user role — a finance user logging in from a Linux device is more suspicious than an engineer doing the same.
Pivot to: For anomalous devices, use Pattern 4 to examine other activity from the same source IP.
Pattern 6: Authentication from a Specific Email Address (T1078 - Valid Accounts)
When to use: During account compromise investigation, when tracking a specific user's authentication activity across all identity providers.
Search for login and preauth events from a specific email address:
QUERY authentication.**
WITH authentication.activity_id IN LOGON, PREAUTH
AND (
authentication.actor.user.email_addr = '[email protected]'
OR authentication.user.email_addr = '[email protected]'
)What to look for: Logins from unusual geographic locations or IP ranges. Simultaneous sessions from different locations (impossible travel). Pre-authentication events that don't lead to a logon may indicate reconnaissance.
Pivot to: Use Pattern 8 to check if this user created any cloud resources. Use Pattern 12 to examine their email sending activity.
Pattern 7: Failed Account Creation Attempts (T1136 - Create Account)
When to use: When hunting for persistence mechanisms or investigating unauthorized access to IAM systems.
Search for all failed account creation attempts, such as in IGA, PAM, or Cloud IAM tools:
QUERY account_change.**
WITH account_change.activity_id = CREATE AND account_change.status_id = FAILUREWhat to look for: Repeated creation failures may indicate an attacker testing permissions or attempting to create backdoor accounts. Pay attention to the actor — is this user authorized to create accounts? Check the target account names for patterns (generic names, service account naming conventions).
Pivot to: Use Pattern 8 to check for successful account creations by the same actor. Use Pattern 6 to examine the actor's authentication history.
Pattern 8: AWS IAM Role Creation from SSO Users (T1098 - Account Manipulation)
When to use: When auditing cloud IAM changes, investigating privilege escalation, or tracking SSO-federated activity in AWS.
Search for successful AWS IAM Role creation events from a specific email address. Uses CONTAINS to search within full session names or AWS IAM ARNs:
QUERY account_change.**
WITH account_change.activity_id = CREATE
AND account_change.status_id = SUCCESS
AND account_change.cloud.provider = 'AWS'
AND (
account_change.actor.user.uid CONTAINS '[email protected]'
OR account_change.actor.user.uid_alt CONTAINS '[email protected]'
) SINCE 1monthWhat to look for: Roles with overly permissive policies (e.g., AdministratorAccess). Roles created outside of change management windows. Roles created by users who don't normally perform IAM operations.
Pivot to: Search api_activity for any API calls made using the newly created role. Use Pattern 6 to verify the actor's identity and login history.
Pattern 9: SSH Tunnels Spawned from a Browser (T1572 - Protocol Tunneling)
When to use: When hunting for data exfiltration channels or unauthorized tunneling through web-based SSH clients.
Look for SSH activity that originated from a browser process. This pattern can be adapted for other event classes such as process_activity or file_system_activity:
QUERY ssh_activity.**
WITH (
ssh_activity.actor.process.cmd_line CONTAINS 'brave'
OR ssh_activity.actor.process.cmd_line CONTAINS 'firefox'
OR ssh_activity.actor.process.cmd_line CONTAINS 'chrome'
OR ssh_activity.actor.process.cmd_line CONTAINS 'explorer'
)What to look for: Any SSH activity where the parent process is a web browser is unusual. Check the destination IPs and ports — tunnels to external IPs are higher risk. Look at the timing — activity outside business hours is more suspicious.
Pivot to: Use Pattern 11 to check if these SSH sessions transferred significant data. Use Pattern 10 to investigate the destination IPs.
Pattern 10: SSH Activity from a Specific IP (T1021.004 - Remote Services: SSH)
When to use: When investigating a known suspicious IP for SSH-based lateral movement or remote access.
Search SSH logs for a specific IP across all IP fields. You could also use the %ip entity if all IP paths are mapped:
QUERY ssh_activity.**
WITH (
ssh_activity.src_endpoint.ip = '10.100.1.16'
OR ssh_activity.dst_endpoint.ip = '10.100.1.16'
OR ssh_activity.proxy.ip = '10.100.1.16'
OR ssh_activity.load_balancer.ip = '10.100.1.16'
OR ssh_activity.load_balancer.src_endpoint.ip = '10.100.1.16'
OR ssh_activity.load_balancer.dst_endpoint.ip = '10.100.1.16'
)What to look for: Unexpected SSH connections — especially to or from servers that shouldn't have SSH access. Check the user associated with the session. Multiple destination hosts from the same source may indicate lateral movement.
Pivot to: Use Pattern 11 to check data transfer volumes for these sessions. Use Pattern 2 to see other network activity from the same host.
Pattern 11: SSH Data Egress Attempts (T1048 - Exfiltration Over Alternative Protocol)
When to use: When hunting for data exfiltration via SSH/SCP/SFTP, or during incident response when you suspect data was stolen.
Search SSH logs for sessions that transferred more than 40,000 bytes:
QUERY ssh_activity.**
WITH (
ssh_activity.traffic.bytes >= 40000
OR ssh_activity.traffic.bytes_out >= 40000
)What to look for: Large transfers to external IPs. Transfers occurring outside business hours or from hosts that don't normally use SSH. Compare the byte count to baseline — even 40KB may be significant for a host that normally has minimal SSH traffic.
Pivot to: Use Pattern 10 to investigate the destination IPs. Check file_activity for what files were accessed on the source host before the transfer.
Pattern 12: Sent Emails from a Specific Address (T1114 - Email Collection)
When to use: When investigating a compromised account for data exfiltration via email, or auditing a user's outbound email activity.
Search for all outbound emails sent from a specific user. CONTAINS catches cases where the address is prepended/appended by SMTP headers or mail group strings:
QUERY email_activity.**
WITH email_activity.activity_id = SEND
AND (
email_activity.actor.user.email_addr = '[email protected]'
OR email_activity.email.from = '[email protected]'
OR email_activity.email.from_mailbox CONTAINS '[email protected]'
)What to look for: Emails to external domains, especially free email providers (gmail, protonmail). Unusually large attachments. High volume of sends in a short period. Emails sent at unusual times for the user.
Pivot to: Use Pattern 6 to check the user's authentication history for signs of compromise. Use Pattern 14 to see if any of their emails triggered security scans.
Pattern 13: Received Emails from a Specific IP (T1566 - Phishing)
When to use: When you have a suspicious sending IP from threat intel or an alert and want to find all emails delivered from it.
Search for emails received from a specific source IP:
QUERY email_activity.**
WITH email_activity.activity_id = RECEIVE
AND (
email_activity.src_endpoint.ip = '10.100.1.16'
OR email_activity.email.x_originating_ip = '10.100.1.16'
)What to look for: The number of recipients — a wide distribution suggests a phishing campaign. Check the sender addresses for spoofing patterns. Look at subject lines and attachment names for social engineering indicators.
Pivot to: Use Pattern 14 to check if email security tools flagged any of these messages. For recipients who may have clicked, use Pattern 1 to check for subsequent credential compromise.
Pattern 14: Scanned Emails by Severity (T1566 - Phishing)
When to use: When reviewing email security posture, investigating a phishing campaign, or triaging email-based alerts from security tools.
Search alerts and detections for medium-to-critical severity, such as from Microsoft Defender for Office, Abstract Security, or Proofpoint:
QUERY email_activity.**
WITH email_activity.activity_id = SCAN
AND email_activity.severity_id IN MEDIUM, HIGH, CRITICAL, FATALWhat to look for: Clusters of high-severity detections targeting the same recipients or from the same sender. Detections that were delivered despite being flagged (check disposition_id). CRITICAL/FATAL severity scans warrant immediate investigation.
Pivot to: Use Pattern 13 to find other emails from the same sender IP. For recipients of delivered malicious emails, check process_activity and file_activity for post-compromise indicators.
Pattern 15: Alerts by Malicious Hash (T1204 - User Execution)
When to use: When you have a known malicious file hash from threat intel, a sandbox report, or another alert and want to find all detections involving it.
Search specifically for alerts matching a known malicious SHA-256 hash:
QUERY detection_finding.**
WITH detection_finding.is_alert = TRUE
AND (
detection_finding.evidences.file.hashes.value = '88f5b113543f7a002c51ad20e00933c534daa6b138fa4a75dc3e38f06b36337b'
OR detection_finding.evidences.process.file.hashes.value = '88f5b113543f7a002c51ad20e00933c534daa6b138fa4a75dc3e38f06b36337b'
OR detection_finding.actor.process.file.hashes.value = '88f5b113543f7a002c51ad20e00933c534daa6b138fa4a75dc3e38f06b36337b'
)What to look for: How many hosts have this hash — a single host may be an isolated incident, while many hosts suggest a worm or supply chain compromise. Check the file path and process lineage in the alert evidence to understand how the file arrived. Look at the alert disposition — was it quarantined, allowed, or unknown?
Pivot to: For each affected host, use Pattern 2 to check for lateral movement. Use Pattern 11 to check for data exfiltration. Search process_activity with %hash to find execution of the malicious file that may not have triggered an alert.
Updated about 2 months ago