From SPL to FSQL: A Security Analyst's Guide

Introduction

If you're a security analyst who's comfortable with Splunk's Search Processing Language (SPL), this guide will help you transition to Query's Federated Search Query Language (FSQL). FSQL allows you to search across multiple data sources using a unified language that provides a concise syntax for sophisticated data retrieval and filtering operations.

This tutorial will help you leverage your existing SPL knowledge while highlighting the unique capabilities of FSQL for security analysis tasks.

Why FSQL?

Before diving into the syntax, let's understand why FSQL matters for security analysts:

• Federated search: Query across multiple data sources with a single language
• Text-based queries: Easy to share, copy, paste, version, and include in documentation
• OCSF-based data model: All data sources represented as unified OCSF events
• Powerful selection syntax: Target specific data with a concise, flexible syntax
• Familiar feel: If you know SPL, many concepts will be easy to grasp

FSQL Basics: Transitioning from SPL

Basic Query Structure

In SPL, you typically start with a search term followed by pipes for additional operations:

index=security sourcetype=firewall src_ip="10.0.0.1" | table src_ip, dest_ip, action

In FSQL, the structure is more declarative:

QUERY network_activity.src_endpoint.ip, network_activity.dst_endpoint.ip, network_activity.status_id
WITH network_activity.src_endpoint.ip = '10.0.0.1'

The basic structure is:

QUERY <fields> WITH <filter> [BEFORE <time>] [AFTER <time>] [FROM <connectors>]

You may find it more comfortable to write your filter first and your field selection second:

QUERY <filter> [SHOW <fields>] ...

QUERY network_activity.src_endpoint.ip = '10.0.0.1'

Specifying Time Ranges

SPL:

index=security sourcetype=firewall earliest=-24h latest=-12h

FSQL:

QUERY network_activity.* BEFORE 12hrs AFTER 24hrs

Selecting Data Sources

SPL:

index=security (sourcetype=cisco_asa OR sourcetype=palo_alto)

FSQL:

QUERY network_activity.* FROM 'Cisco ASA', 'Palo Alto'

Field Selection: From SPL to FSQL

Basic Field Selection

SPL:

... | fields src_ip, dest_ip, action

FSQL:

QUERY network_activity.src_endpoint.ip, network_activity.dst_endpoint.ip, network_activity.status_id

Wildcard Field Selection

SPL:

... | fields src_*, action

FSQL:

QUERY network_activity.src_endpoint.*, network_activity.status_id

For all fields in an event:

SPL:

... | fields *

(The above is implicit in most SPL queries)

FSQL:

QUERY network_activity.*

Excluding Fields

SPL:

... | fields - start_time, end_time

FSQL:

QUERY network_activity.(* - (start_time | end_time))

Filtering: SPL vs FSQL

Equality Filters

SPL:

index=myindex src_ip="10.0.0.1"

FSQL:

WITH network_activity.src_endpoint.ip = '10.0.0.1'

Multiple Conditions with AND/OR

SPL:

index=myindex (src_ip="10.0.0.1" OR src_ip="10.0.0.2") AND action="blocked"

FSQL:

WITH (network_activity.src_endpoint.ip = '10.0.0.1' OR network_activity.src_endpoint.ip = '10.0.0.2') 
AND network_activity.status_id = FAILURE

String Operations

SPL:

index=web user_agent LIKE "%Mozilla%"

FSQL:

WITH http_activity.user_agent CONTAINS 'Mozilla'

Case-insensitive in SPL:

index=web LOWER(user_agent) LIKE "%mozilla%"

Case-insensitive in FSQL:

WITH http_activity.user_agent ICONTAINS 'mozilla'

IN Operator

SPL:

index=myindex status IN ("success", "failure")

FSQL:

WITH network_activity.status_id IN SUCCESS, FAILURE

FSQL Power Features for Security Analysts

Event Category Searches

In Splunk, you might be using tag or datamodel:

SPL:

tag=network

datamodel=Network_Traffic

The powerful feature in FSQL is the ability to search across all events in a category using the # selector:

QUERY #network.*
WITH #network.status_id = FAILURE

This searches across all network-related events (http_activity, dns_activity, network_activity, etc.) for failed connections - very useful for security investigations!

Observable Searches (Entity Searches)

In SPL, you might search for an IP across multiple known IP fields or via a raw text search, as below:

index=security (src_ip="10.0.0.1" OR dest_ip="10.0.0.1" OR translated_ip="10.0.0.1")

index=security "10.0.0.1"

In FSQL, use the % observable operator to search across all IP fields:

QUERY #network.*
WITH %ip = '10.0.0.1'

Type-Based Searches

Search for all user information:

QUERY **:type(user)

Or all device information with a specific IP:

QUERY **:type(device) 
WITH @ip = '10.0.0.1'

Common Security Use Cases: SPL to FSQL Translation

1. Failed Authentication Attempts

SPL:

index=security sourcetype=windows_event_log EventCode=4625 
| stats count by user, src_ip 
| where count > 5

FSQL:

QUERY authentication.user.username, authentication.src_endpoint.ip
WITH authentication.status_id = FAILURE

Note: FSQL doesn't have built-in aggregation yet, so you'd need to analyze the results after retrieval.

2. Suspicious Process Execution

SPL:

index=security sourcetype=windows_event_log EventCode=4688 process_name IN ("powershell.exe", "cmd.exe") command_line LIKE "%bypass%" OR command_line LIKE "%hidden%" 
| table _time, user, process_name, command_line

FSQL:

QUERY process_activity.time, process_activity.user.username, 
       process_activity.process.name, process_activity.process.cmd_line
WITH process_activity.process.name IN 'powershell.exe', 'cmd.exe'
AND (process_activity.process.cmd_line CONTAINS 'bypass' OR 
     process_activity.process.cmd_line CONTAINS 'hidden')

3. Suspicious Network Connections

SPL:

index=security sourcetype=firewall (dest_port=4444 OR dest_port=8080) 
| table _time, src_ip, dest_ip, dest_port, bytes_out, bytes_in

FSQL:

QUERY network_activity.time, network_activity.src_endpoint.ip,
       network_activity.dst_endpoint.ip, network_activity.dst_endpoint.port,
       network_activity.src_endpoint.bytes, network_activity.dst_endpoint.bytes
WITH network_activity.dst_endpoint.port IN 4444, 8080

4. Searching Across Multiple Data Sources

SPL:

(index=security sourcetype=firewall src_ip="10.0.0.1") OR
(index=security sourcetype=windows_event_log user="jsmith")
| stats count by sourcetype

FSQL:

QUERY network_activity.*, authentication.*
WITH network_activity.src_endpoint.ip = '10.0.0.1' OR
     authentication.user.username = 'jsmith'
FROM 'Firewall Logs', 'Windows Event Logs'

FSQL Quick Reference

Operators

Selectors

Tips for SPL Users

1. Think in terms of events: SPL is pipe-based and can transform data significantly. FSQL is more about selecting and filtering OCSF events.

2. Learn the data model: Take time to understand the OCSF schema and how your sources map to it.

3. Use observable shortcuts: The %ip notation is much easier than remembering all the places IPs might appear.

4. Leverage wildcards: Use * and type selectors to get what you need without knowing the exact field paths.

5. Start simple: Begin with basic queries and expand as you get comfortable.

Conclusion

As you transition from SPL to FSQL, you'll find that your experience with SPL gives you a solid foundation. FSQL's concise syntax and powerful selectors can make your security investigations even more efficient once you get comfortable with them.

Remember that FSQL is optimized for querying across multiple data sources with a unified schema, which is perfect for modern security operations where data lives in many different systems.

Happy hunting!