コンテンツにスキップ

System Tables

CSIRT-Pro provides system tables that are created automatically for each organization.

These tables hold internal platform data such as cases (threads), threat intelligence, and chat messages. Just like user-created Pipelines, you can search and analyze them with PRQL.

Available System Tables

Table Description Primary Use
messages Body messages of cases (threads) Case body search, statistics, reporting
message_meta Case metadata such as status and classification Aggregation by status / classification
reply_messages Reply messages on cases Investigation comment search, team activity analysis
vulnerabilities Vulnerability (CVE) data Vulnerability search, impact assessment
indicators Threat indicator (IOC) data IOC search, threat hunting

System Table Restrictions

  • System table names are reserved and cannot be used as Pipeline names.
  • System tables are read-only (data is managed automatically by the platform).
  • You query them with PRQL from the UI just like a regular Pipeline (the required internal settings are applied automatically).

Where status and classification live

A case's status (status) and classification (classification) are stored in message_meta, not in messages. To aggregate by status or classification, query message_meta.

messages (Case Body Table)

Stores the body messages of cases (threads).

Key Fields

Field Type Description
__time DateTime Timestamp
message_id String Message / case ID
thread_id String Thread ID
user String Author
content String Message body
created_at DateTime Creation time
reply_to String ID of the message being replied to
tags Map(String, String) Tags (with keys such as severity)
embedding_vector Array Embedding vector used for similar-case search

Query Examples

# Most recent case bodies
from `messages`
sort {-__time}
take 50

# Filter by severity extracted from tags
from `messages`
derive {severity = get_map tags "severity"}
filter severity == "high"
sort {-__time}

# Count by author
from `messages`
group {user} (
  aggregate {cnt = count this}
)
sort {-cnt}

message_meta (Case Metadata Table)

Stores case metadata such as status and classification.

Key Fields

Field Type Description
__time DateTime Timestamp
message_id String Target case ID
thread_id String Thread ID
status String Status (open / inprogress / resolved, etc.)
classification String Classification
updated_by String Last updater
updated_at DateTime Last update time
tags Map(String, String) Tags

Query Examples

# Open cases
from `message_meta`
filter status == "open"
sort {-__time}
take 50

# Case count by classification
from `message_meta`
group {classification} (
  aggregate {cnt = count this}
)
sort {-cnt}

# Case count by status
from `message_meta`
group {status} (
  aggregate {cnt = count this}
)
sort {-cnt}

reply_messages (Reply Messages Table)

Stores investigation comments and reply messages posted to cases.

Key Fields

Field Type Description
__time DateTime Reply timestamp
message_id String Message ID
thread_id String Parent case thread ID
user String Author of the reply
content String Reply content
reply_to String ID of the message being replied to

Query Examples

# Recent investigation comments
from `reply_messages`
sort {-__time}
take 100

# Analyst activity over the last 30 days
from `reply_messages`
filter __time > (now) - (to_interval_day 30)
group {user} (
  aggregate {reply_count = count this}
)
sort {-reply_count}

vulnerabilities (Vulnerability Table)

Stores vulnerability information collected from CVE databases.

Key Fields

Field Type Description
id String CVE ID (e.g., CVE-2024-12345)
product String Affected product name
severity String Severity (CRITICAL / HIGH / MEDIUM / LOW)
kev Boolean Whether it is a Known Exploited Vulnerability (KEV)
fixed_in String Fixed version

No __time column

vulnerabilities has no __time, so sort {-__time} cannot be used. Sort by an existing field such as id or severity.

Query Examples

# Critical vulnerabilities
from `vulnerabilities`
filter severity == "CRITICAL"
sort {id}
take 50

# Search vulnerabilities by product
from `vulnerabilities`
filter product ~= "nginx"

# Known Exploited Vulnerabilities (KEV) only
from `vulnerabilities`
filter kev == true

# Count by severity
from `vulnerabilities`
group {severity} (
  aggregate {cnt = count this}
)
sort {-cnt}

indicators (Threat Indicator Table)

Stores IOC (Indicator of Compromise) data collected from threat feeds.

Key Fields

Field Type Description
__time DateTime Timestamp
value String IOC value (IP, domain, hash, etc.)
indicator_type String Indicator type (ip-src / domain / md5 / sha256, etc.)
category String Threat category
threat_level String Threat level (high / medium / low)
feed_name String Feed name
feed_provider String Feed provider
first_seen / last_seen DateTime First / last observed time
comment String Notes
tags Array Related tags

Query Examples

# High-risk IOCs
from `indicators`
filter threat_level == "high"
sort {-__time}
take 100

# IP-source indicators
from `indicators`
filter indicator_type == "ip-src"
sort {-__time}
take 50

# Look up a specific IOC
from `indicators`
filter value == "192.168.1.100"

# IOC count by feed
from `indicators`
group {feed_name} (
  aggregate {cnt = count this}
)
sort {-cnt}

Joining Tables

You can join system tables with each other, or with user-created Pipelines, for correlated analysis.

Example: Join case body with status (messages × message_meta)

from `messages`
join `message_meta` (==message_id)
filter message_meta.status == "open"
select {messages.__time, messages.content, message_meta.status, message_meta.classification}
sort {-messages.__time}
take 50

Example: Match firewall logs against IOCs

Replace firewall_logs with the name of a Pipeline you created.

from `firewall_logs`
join `indicators` (this.src_ip == that.value)
filter indicators.threat_level == "high"
select {__time, src_ip, dst_ip, action, indicators.category, indicators.feed_name}
sort {-__time}
take 100

Using System Tables in the Search UI

To query system tables from the Search screen:

  1. Enter a system table name such as frommessages`` in the query editor.
  2. Search and visualize exactly as you would with a regular query (the required internal settings are applied automatically by the UI).

Field list

The field list (left panel) shows the available fields for the selected table. If you are unsure about a field name's spelling, check the field list first.