FSQL User Guide for Security Analysts
What is FSQL?
Federated Search Query Language (FSQL) is Query's unified language for searching across multiple security data sources. It provides security analysts with a powerful yet approachable way to retrieve, filter, and analyze security data from various platforms through a single interface.
Getting Started with FSQL
Basic Query Structure
Every FSQL query follows this general pattern:
QUERY <fields to return>
WITH <filter conditions>
[BEFORE <end time>]
[AFTER <start time>]
[FROM <connector list>]
Example:
QUERY authentication.user.username, authentication.src_endpoint.ip
WITH authentication.status_id = FAILURE
BEFORE 24hrs AFTER 48hrs
FROM 'Active Directory', 'Okta'
This query returns failed authentication attempts from the past 24-48 hours, showing usernames and source IPs from both Active Directory and Okta.
Time Ranges
Time ranges are specified with BEFORE
and AFTER
keywords:
BEFORE 24hrs // Data from up to 24 hours ago
AFTER 48hrs // Data from at least 48 hours ago
Times can be specified as being relative to the current time, as absolute dates in ISO 8601 format, or as epoch timestamps.
Examples:
QUERY ... BEFORE 24hrs
QUERY ... AFTER 7d
QUERY ... AFTER 1 month
QUERY ... AFTER '2025-03-17'
QUERY ... BEFORE '2025-04-01 17:30:00'
QUERY ... BEFORE 1746109163
For more on specifying dates and times, see the FSQL Technical Reference.
Data Sources
Specify data sources with the FROM
keyword followed by connector names in quotes:
FROM 'My S3 Bucket', 'Crowdstrike Falcon', 'AWS CloudTrail'
Security Analysis with FSQL
Finding Security Events
The simplest way to find security events is to query a specific event type, in the example below the :depth()
and .**
commands are used to retrieve every possible mapped field from the furthest possible depth in the schema for authentication events:
QUERY authentication.:depth(8).**
WITH authentication.status_id = FAILURE
For a broader search across all network-related events the following command, note the usage of #network.*
to retrieve all events within the network
category, followed by .**
to retrieve all nested fields to a default depth of 3 in the schema. You should use the FROM
command to specify Connectors or this type of command runs the risk of timing out:
QUERY #network.*.**
WITH #network.status_id = FAILURE
FROM 'My TGW Logs', 'NetskopeLogs', 'Akamai FW Logs', 'FDRNetworkEvents'
Tracking Entity Activity
To track activity associated with an IP address across all data sources, use the %ip
filter in your WITH
statement to look for mapped events that have 10.0.0.1
mapped to the IP Address Entity (Observable):
QUERY #network.*.**
WITH %ip = '10.0.0.1'
To find all processes launched by a specific user across all process activity events to their fullest depth, you can specify the attribute using WITH process_activity.user.name = 'jsmith'
to add precision to your query:
QUERY process_activity.:depth(8).**
WITH process_activity.user.name = 'jsmith'
Identifying Suspicious Activity
For suspicious command-line activity, consider the following command that uses the CONTAINS
operator to perform a fuzzy search within mapped command lines:
QUERY process_activity.:depth(8).**
WITH process_activity.process.cmd_line CONTAINS 'mimikatz'
OR process_activity.process.cmd_line CONTAINS 'bypass'
For unusual outbound connections, you can search network_activity
events using a WITH
filter on dst_endpoint.port
which is always the outbound side of the network connectivity:
QUERY network_activity.:depth(8).**
WITH network_activity.dst_endpoint.port IN 4444, 8080, 6666
Field Selection Techniques
Basic Field Selection
To select specific fields:
QUERY authentication.time, authentication.user.username, authentication.src_endpoint.ip
Wildcards and Expansions
To select all fields to their fullest depth from an event:
QUERY authentication.:depth(8).**
To select all fields except start and end time:
QUERY authentication.(* - (start_time + end_time))
Working with Multiple Event Types
To query across multiple specific event types and retrieve a single field:
QUERY (authentication + process_activity).user.name
To query all events in a category to the default depth of 3:
QUERY #network.*.**
Advanced Filtering
Comparison Operators
Operator | Description | Example |
---|---|---|
= | Equals | user.username = 'admin' |
== | Case-insensitive equals | user.username == 'ADMIN' |
!= | Not equals | status_id != SUCCESS |
CONTAINS or ~ | Contains substring | cmd_line CONTAINS 'powershell' |
ICONTAINS or ~~ | Case-insensitive contains | cmd_line ICONTAINS 'powershell' |
STARTSWITH or ^= | Starts with | filename STARTSWITH 'mal' |
ENDSWITH or $= | Ends with | filename ENDSWITH '.exe' |
IN | In list | status_id IN SUCCESS, FAILURE |
< , > , <= , >= | Numeric comparisons | count > 5 |
empty | Field is empty | user.email empty |
Combining Filters
Use parentheses with AND
and OR
operators to create complex filters:
QUERY process_activity.:depth(8).**
WITH (process_activity.process.name IN 'powershell.exe', 'cmd.exe')
AND (process_activity.process.cmd_line CONTAINS 'hidden' OR process_activity.process.cmd_line CONTAINS 'encode')
List Filters with ANY and ALL
For array fields, use ANY
or ALL
quantifiers.
QUERY api_activity.:depth(8).**
WITH ANY web_resources_activity.web_resources.name CONTAINS 'aws'
QUERY api_activity.:depth(8).**
WITH ALL api_activity.actor.authorizations.policy.name IN 'Global Administrator', 'Security Administrator'
Observable and Type Searches
Observable Searches
Use the %
symbol to search by observable type:
Observable | Usage | Description |
---|---|---|
%ip | WITH %ip = '10.0.0.1' | Finds IP across all applicable fields |
%domain | WITH %domain = 'evil.com' | Finds domain across all applicable fields |
%email | WITH %email = '[email protected]' | Finds email across all applicable fields |
%hash | WITH %hash = '44d88612fea8a8f36de82e1278abb02f' | Finds file hash across all applicable fields |
%username | WITH %username = 'admin' | Finds username across all applicable fields |
Type-Based Searches
Use the @
symbol to search by data type:
QUERY **:type(device)
WITH @ip = '10.0.0.1'
QUERY **:type(user)
WITH @email CONTAINS 'example.com'
Common Security Investigation Patterns
Investigation Pattern 1: Authentication Analysis
To find failed login attempts for a specific user:
QUERY authentication.:depth(8).**
WITH authentication.user.username = 'jsmith'
AND authentication.status_id = FAILURE
BEFORE 24hrs
Investigation Pattern 2: Lateral Movement Detection
To find potential lateral movement involving a compromised host:
QUERY #network.*.**
WITH #network.src_endpoint.hostname = 'WORKSTATION123'
AND #network.dst_endpoint.port IN 445, 1433, 8080
Investigation Pattern 3: Find Failed MFA Logins
Searches all Authentication events for failed logins with MFA, such as from Okta, Auth0, or Google Workspace Login logs.
QUERY authentication.:depth(8).**
WITH authentication.is_mfa = TRUE AND authentication.activity_id = LOGON AND authentication.status_id = FAILURE
Investigation Pattern 4: Find Authentication from a Specific IP
Searches all failed or successful Authentication events from the IP address 10.100.1.16
QUERY authentication.:depth(8).**
WITH authentication.status_id IN SUCCESS, FAILURE AND authentication.src_endpoint.ip = '10.100.1.16'
Investigation Pattern 5: Find Logins from devices with a specific OS
Searches for logons from MacOS devices, note that os.type_id
is an enumeration, and the value does not need to be enclosed in single-quotes.
QUERY authentication.:depth(8).**
WITH authentication.activity_id = LOGON AND authentication.device.os.type_id = MACOS
Investigation Pattern 6: Find Logon/Auth from specific Email Address
Searches for login and preauth events from a specific email address.
QUERY authentication.:depth(8).**
WITH authentication.activity_id IN LOGON, PREAUTH
AND (
authentication.actor.user.email_addr = '[email protected]'
OR authentication.user.email_addr = '[email protected]'
)
Investigation Pattern 7: Find failed account creation attempts
Searches for all failed account creation attempts, such as in your IGA, PAM, or Cloud IAM tools (e.g., AWS IAM Roles failed to created).
QUERY account_change.:depth(8).**
WITH account_change.activity_id = CREATE AND account_change.status_id = FAILURE
Investigation Pattern 8: AWS IAM Role creation from SSO Users
Searches for all successful AWS IAM Role creation events from a specific email address, use the CONTAINS
search to search within the full session names or AWS IAM ARNs in the last month.
QUERY account_change.:depth(8).**
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]'
) AFTER 1month
Investigation Pattern 9: SSH Tunnels spawned from a Browser
Looks for SSH logs that originated from a browser, this could be swapped to other event classes such as process_activity
or file_system_activity
as well.
QUERY ssh_activity.:depth(8).**
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'
)
Investigation Pattern 10: SSH Activity from a specific IP
Searches for SSH logs that look for IPs anywhere in the log that match 10.100.1.16
. You could also modify this search to use the %ip
Entity (Observable) instead, if you have mapped all of the possible IP paths to the IP Address Entity (Observable).
QUERY ssh_activity.:depth(8).**
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'
)
Investigation Pattern 11: SSH Data Egress Attempts
Searches SSH logs that transfer more than 40000 bytes.
QUERY ssh_activity.:depth(8).**
WITH (
ssh_activity.traffic.bytes >= 40000
OR ssh_activity.traffic.bytes_out >= 40000
)
Investigation Pattern 12: Sent Emails from a specific Email Address
Searches for all outbound emails sent from a specific user, by their email address, anywhere where the sender email can appear. You can simplify this query using the %email
Entity (Observable) if it is mapped. The CONTAINS
operator is used to find the email even when it is prepended/appended by extra characters from SMTP headers or mail groups.
QUERY email_activity.:depth(8).**
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]'
)
Investigation Pattern 13: Received Emails from a specific IP Address
Searches from emails sent from 10.100.1.16.
QUERY email_activity.:depth(8).**
WITH email_activity.activity_id = RECEIVE
AND (
email_activity.src_endpoint.ip = '10.100.1.16'
OR email.x_originated_ip = '10.100.1.16'
)
Investigation Pattern 14: Find Scanned Emails by Severity
Searches alerts and/or detections for high or critical severity such as from Microsoft Defender for Office or logs stored in a data lake or warehouse from an email gateway or DLP tool, such as Abstract Security or Proofpoint.
QUERY email_activity.:depth(8).**
WITH email_activity.activity_id = SCAN
AND email_activity.severity_id IN MEDIUM, HIGH, CRITICAL, FATAL
Investigation Pattern 15: Find Alerts by Malicious Hash
Searches specifically for alerts with the sha256 hash: 88f5b113543f7a002c51ad20e00933c534daa6b138fa4a75dc3e38f06b36337b
QUERY detection_finding.:depth(8).**
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'
)
Updated about 14 hours ago