FSQL for KQL Users

Introduction

If you're a security analyst experienced with Microsoft's Kusto Query Language (KQL) — whether from Sentinel, Defender, or Log Analytics — this guide will help you transition to Query's Federated Search Query Language (FSQL). FSQL lets you search across all your security data sources with a single language, using a consistent OCSF-based schema.

Your KQL experience gives you a strong foundation. This guide maps the concepts you already know to their FSQL equivalents.

Why FSQL?

• Federated search: Query across Microsoft, AWS, CrowdStrike, Okta, and dozens more — with one language.
• OCSF-based data model: No more memorizing different table schemas per product. All data is normalized.
• Observable shortcuts: Search for an IP, hash, or username across all fields with a single % selector.
• Text-based queries: Easy to share, version, and include in runbooks.

Basic Query Structure

KQL:

SigninLogs
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress

FSQL:

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

The core structural difference: KQL is pipe-based (table → filter → project → extend → summarize), while FSQL is declarative — you specify what to show, how to filter, and the time range in a single statement.

QUERY <fields> [WITH <filters>] [SINCE <time>] [UNTIL <time>] [FROM <connectors>]

Field Selection

Selecting specific fields

KQL:

SigninLogs
| project TimeGenerated, UserPrincipalName, IPAddress, Location

FSQL:

QUERY authentication.time, authentication.user.username, authentication.src_endpoint.ip, authentication.src_endpoint.location.country

All fields

KQL:

SigninLogs

FSQL:

QUERY authentication.**

The ** expansion returns all fields to full depth. Use * for just the top level.

Excluding fields

KQL:

SigninLogs
| project-away TimeGenerated, Type

FSQL:

QUERY authentication.(* - (start_time + end_time))

Filtering

Basic comparison

KQL:

SigninLogs
| where UserPrincipalName == "[email protected]"

FSQL:

WITH authentication.user.email_addr = '[email protected]'

Note: In FSQL, = is case-sensitive equals and == is case-insensitive equals. This is the reverse of KQL, where == is case-sensitive and =~ is case-insensitive.

String operations

KQL:

SecurityEvent
| where CommandLine contains "powershell"

FSQL:

WITH process_activity.process.cmd_line ICONTAINS 'powershell'

Note: KQL string operators like contains, startswith, and endswith are case-insensitive by default. For the closest behavior match in FSQL, prefer the case-insensitive variants like ICONTAINS, ISTARTSWITH, and IENDSWITH.

Multiple values

KQL:

SigninLogs
| where ResultType in ("50126", "50053", "50074")

FSQL:

WITH authentication.status_id IN FAILURE, OTHER

Note: FSQL uses OCSF enumerations (like FAILURE, SUCCESS) rather than vendor-specific codes. This means the same filter works across Okta, Azure AD, AWS IAM, and every other connected source.

Combining filters

KQL:

SigninLogs
| where UserPrincipalName == "[email protected]" and ResultType != "0"
| where IPAddress startswith "10."

FSQL:

WITH authentication.user.email_addr = '[email protected]'
AND authentication.status_id != SUCCESS
AND authentication.src_endpoint.ip ISTARTSWITH '10.'

CIDR filtering

KQL:

SigninLogs
| where ipv4_is_in_range(IPAddress, "10.0.0.0/8")

FSQL:

WITH authentication.src_endpoint.ip CIDR '10.0.0.0/8'

Or across all IP fields:

WITH %ip CIDR '10.0.0.0/8'

Regex

KQL:

SecurityEvent
| where CommandLine matches regex @".*\.(ps1|bat|vbs)$"

FSQL:

WITH process_activity.process.cmd_line MATCHES '.*\.(ps1|bat|vbs)$'

Note: Regex support varies between data source platforms. Some sources may not support regex natively, which can result in over-fetching.

Time Ranges

KQL:

SigninLogs
| where TimeGenerated > ago(24h) and TimeGenerated < ago(12h)

FSQL:

QUERY authentication.** SINCE 24hrs UNTIL 12hrs

SINCE is where the window starts (how far back), UNTIL is where it ends. KQL's ago() values map directly to FSQL's relative time syntax.

Ordering Results

KQL:

SigninLogs
| where ResultType != "0"
| sort by TimeGenerated desc

FSQL:

QUERY authentication.**
WITH authentication.status_id = FAILURE
ORDER BY authentication.time DESC

Multiple sort keys work the same way — comma-separated, each with an optional ASC or DESC (defaults to ASC):

ORDER BY authentication.src_endpoint.ip ASC, authentication.time DESC

Aggregation

KQL:

SigninLogs
| where ResultType != "0"
| summarize count() by UserPrincipalName

FSQL:

SUMMARIZE COUNT authentication
WITH authentication.status_id = FAILURE
GROUP BY authentication.user.username

FSQL Power Features for KQL Users

Observable (entity) searches

In KQL, searching for an IP across multiple tables and columns requires unions and multiple where clauses. In FSQL, use the % entity shortcut:

QUERY **
WITH %ip = '10.0.0.1'

This searches every IP field across every event type — no need to know field names.

For large environments, add a time bound or result cap early, such as SINCE 24hrs or LIMIT 100, to keep investigations responsive.

Category searches

Instead of querying specific tables, search an entire category:

QUERY #network.**
WITH #network.dst_endpoint.port IN 4444, 8080, 6666

This searches network_activity, dns_activity, http_activity, ssh_activity, and all other network events at once.

Federated data sources

Scope your search to specific connectors:

QUERY authentication.**
WITH authentication.status_id = FAILURE
FROM 'Azure AD', 'Okta', 'AWS IAM'

Besides display names, connectors can be referenced by alias (a stable, immutable identifier — best for saved queries) or by tag with a # prefix, which expands to every connector carrying that tag:

QUERY authentication.**
WITH authentication.status_id = FAILURE
FROM #identity

See Data Sources (FROM) for the full set of reference types.

Common Security Use Cases

1. Failed sign-ins with location

KQL:

SigninLogs
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName, IPAddress, Location
| sort by TimeGenerated desc

FSQL:

QUERY authentication.time, authentication.user.username, authentication.src_endpoint.ip, authentication.src_endpoint.location.country
WITH authentication.status_id = FAILURE
ORDER BY authentication.time DESC

2. Suspicious process execution

KQL:

DeviceProcessEvents
| where FileName in~ ("powershell.exe", "cmd.exe")
| where ProcessCommandLine has "hidden" or ProcessCommandLine has "encoded"

FSQL:

QUERY process_activity.**
WITH process_activity.process.name IIN 'powershell.exe', 'cmd.exe'
AND (process_activity.process.cmd_line ICONTAINS 'hidden' OR process_activity.process.cmd_line ICONTAINS 'encoded')

3. Outbound connections to unusual ports

KQL:

DeviceNetworkEvents
| where RemotePort in (4444, 8080, 6666)
| project Timestamp, DeviceName, RemoteIP, RemotePort

FSQL:

QUERY network_activity.time, network_activity.device.hostname, network_activity.dst_endpoint.ip, network_activity.dst_endpoint.port
WITH network_activity.dst_endpoint.port IN 4444, 8080, 6666

4. Email from suspicious sender

KQL:

EmailEvents
| where SenderFromAddress contains "suspicious-domain.com"
| where DeliveryAction == "Delivered"

FSQL:

QUERY email_activity.**
WITH email_activity.email.from ICONTAINS 'suspicious-domain.com'
AND email_activity.activity_id = RECEIVE

5. Cross-source IP investigation

KQL (requires multiple queries or union):

union SigninLogs, DeviceNetworkEvents, EmailEvents
| where IPAddress == "10.100.1.16" or RemoteIP == "10.100.1.16" or SenderIPv4 == "10.100.1.16"

FSQL (one query, all sources):

QUERY **
WITH %ip = '10.100.1.16'

Quick Reference: KQL → FSQL

Tips for KQL Users

1. Case sensitivity is reversed. = is case-sensitive in FSQL (unlike KQL's ==). Use == in FSQL for case-insensitive equality.

2. No pipe chains. FSQL doesn't have extend or join. It's designed for searching and filtering, not data transformation. Use ORDER BY for sorting and Subqueries for multi-step correlations.

3. Think OCSF, not tables. Instead of SigninLogs or DeviceProcessEvents, think authentication or process_activity. The Data Model Primer maps common concepts to OCSF paths.

4. Entity shortcuts are your friend. The %ip, %username, %hash shortcuts replace the need for unions across multiple tables and column names.

5. Match KQL string behavior deliberately. KQL string operators are often case-insensitive by default, so in FSQL you'll usually want ICONTAINS, ISTARTSWITH, IENDSWITH, or IIN for the closest behavioral match.

6. Start broad, then refine. Use ** to see all fields first, then narrow to specific paths once you know what's available. Use EXPLAIN ATTRIBUTES to discover field paths.

Conclusion

As you transition from KQL to FSQL, you'll find that your KQL fundamentals carry over well: define the scope, filter precisely, and focus output on investigation-relevant fields.

Use these patterns as a starting point, then adapt them to your own environment and detections.