Query Syntax

fsPulse provides a flexible, SQL-like query language for exploring scan results. This language supports filtering, custom column selection, ordering, and limiting the number of results.

Query Structure

Each query begins with one of the five supported domains:

• roots
• scans
• items
• versions
• hashes

You can then add any of the following optional clauses:

DOMAIN [WHERE ...] [GROUP BY ...] [SHOW ...] [ORDER BY ...] [LIMIT ...] [OFFSET ...]

Column Availability

Each domain has a set of available columns. Columns marked as default are shown when no SHOW clause is specified.

roots Domain

scans Domain

items Domain

The items domain queries item identity — the permanent properties of each tracked file or directory.

versions Domain

The versions domain queries individual item version rows — each representing a distinct state of an item over a temporal range. Filter with is_current:(T) to query only the latest version of each item.

hashes Domain

The hashes domain queries hash observation records — each representing a SHA-256 hash computed for an item version during a scan.

The WHERE Clause

The WHERE clause filters results using one or more filters. Each filter has the structure:

column_name:(value1, value2, ...)

Values must match the column’s type. You can use individual values, ranges (when supported), or a comma-separated combination.

Date Filter Formats

Date columns accept three input forms, matching the three display formats available via @short, @full, and @timestamp. Any value produced by a query can be used directly as filter input.

These forms can be mixed freely within a filter or range:

# Date-only range
started_at:(2025-01-01..2025-01-31)

# Exact time range
started_at:(2025-01-15 08:00:00..2025-01-15 17:00:00)

# Mixed forms in a range
started_at:(2025-01-15..2025-01-16 14:30:00)
mod_date:(1737936000..2025-02-01)

# Multiple values (OR'd)
started_at:(2025-01-15, 2025-02-01 09:00:00, 1737936000)

Combining Filters

When specifying multiple values within a single filter, the match is logically OR. When specifying multiple filters across different columns, the match is logically AND.

For example:

scans where started_at:(2025-01-01..2025-01-07, 2025-02-01..2025-02-07), is_hash:(T)

This query matches scans that:

• Occurred in either the first week of January 2025 or the first week of February 2025
• AND were performed with hashing enabled

The SHOW Clause

The SHOW clause controls which columns are displayed and how some of them are formatted. If omitted, a default column set is used.

You may specify:

• A list of column names
• The keyword default to insert the default set
• The keyword all to show all available columns

Formatting modifiers can be applied using the @ symbol:

item_path@name, mod_date@short

Format Specifiers by Type

All three date display formats (@short, @full, @timestamp) produce values that can be used directly as date filter input — see Date Filter Formats above.

The GROUP BY Clause

Groups rows by one or more columns and enables aggregate functions in the SHOW clause. When GROUP BY is used, a SHOW clause is required.

versions where is_current:(T), root_id:(1) group by file_extension show file_extension, count(*), sum(size) order by sum(size) desc

Aggregate Functions

Rules

• Every non-aggregate column in SHOW must also appear in GROUP BY
• Aggregate functions can be used in ORDER BY (e.g., order by count(*) desc)

The ORDER BY Clause

Specifies sort order for the results. Supports both column names and aggregate expressions.

items order by mod_date desc, item_path asc
scans group by root_id show root_id, count(*) order by count(*) desc

If direction is omitted, ASC is assumed.

The LIMIT and OFFSET Clauses

LIMIT restricts the number of rows returned. OFFSET skips a number of rows before returning results.

items limit 50 offset 100

Examples

# Items whose path contains 'reports'
items where item_path:('reports')

# All PDF items
items where file_extension:('pdf')

# Current state of large files, sorted by size
versions where is_current:(T), item_type:(F), size:(> 1048576) show item_path, size order by size desc

# Version history for a specific item
versions where item_id:(42) order by first_scan_id

# Deleted versions across all roots
versions where is_deleted:(true) show item_path, item_type, first_scan_id, last_scan_id

# Versions with validation failures
versions where val_state:(I) show default, val_error order by first_scan_id desc

# Suspect hash observations
hashes where hash_state:(S) show item_path, item_version, file_hash

# All hash observations for a specific item
hashes where item_id:(42) order by first_scan_id

# Scans with timestamps for programmatic processing
scans show scan_id, started_at@timestamp, file_count order by started_at desc limit 10

# Scans with change and integrity counts
scans show scan_id, file_count, total_size, add_count, modify_count, delete_count, new_hash_suspect_count, new_val_invalid_count order by started_at desc

# File count and total size by extension
versions where is_current:(T), root_id:(1), item_type:(F) group by file_extension show file_extension, count(*), sum(size) order by sum(size) desc

# Scan count per root
scans group by root_id show root_id, count(*), max(total_size), max(file_count) order by count(*) desc

# Hash state distribution
hashes group by hash_state show hash_state, count(*)

# Validation failures by root
versions where val_state:(I) group by root_id show root_id, count(*)

See also: Data Explorer · Validators · Configuration