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

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:

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'
)