Advanced Queries

Utilize the full power of Clickhouse to inspect and alert on your data. Wirespeed utilizes OCSF to ingest events. We support the full OCSF schema, but are only utilizing a subset of fields at the moment. If you do not see a table or field below that you need, please contact support.

​

Quick Start

To run an advanced query, navigate to the Events page and select the icon in the top right.

​

Schema

The events table is the largest and most verbose table in our SIEM. Large or unbounded queries to this table will likely timeout or hit resource limits. It is suggested to always include a time filter when querying this or any other table.

We stringify a majority of the JSON fields in the OCSF schema to optimize for storage and query performance. You will see this with fields like src_endpoint. To query these fields you will want to use utility fields from Clickhouse like JSONExtractString(src_endpoint, 'ip') to extract their unique fields. Utilize the OCSF schema docs for understanding what fields are available on those objects.

​

Tables

• events - All OCSF events
• authentication_events - All events with a class name of “Authentication”

The tables above are filtered to only include the declared events, when running a query for authentication events you will consume your limits much slower by querying the authentication_events table, since it will not require filtering all of your global events. All columns present in the events table are present in the respective class event tables as well.

​

Views

These views precompute data on a recurring basis (typically daily) and expose statistics about behavior in your environment.

​

Email-based views

• authentication_email_asn - Tracks login patterns by ASN per user
• authentication_email_location - Identifies common login locations per user
• authentication_email_ip - Detects common IP access patterns per user
• authentication_email_user_agent - Detects common browser/device usage per user
• authentication_email_hour_of_day - Detects common login time patterns per user

SELECT
    *
FROM
    authentication_events ae
WHERE
    status_id = 1
    AND (
        JSONExtractString(src_endpoint, 'location', 'country'),
        `user.email_addr`
    ) NOT IN (
        SELECT
            country,
            email_addr
        FROM
            authentication_email_location
        GROUP BY
            country,
            email_addr
        HAVING
            sum(success_count) > 0
    )
    AND ingested_at > now() - INTERVAL 1 DAY;

​

Team-based views

• authentication_team_asn - Tracks network provider usage across the organization
• authentication_team_location - Identifies organizational access patterns globally
• authentication_team_ip - Detects suspicious IP sources across the organization
• authentication_team_user_agent - Tracks browser/device usage across the organization
• authentication_team_hour_of_day - Analyzes common hours of activity across the organization

SELECT
    *
FROM
    authentication_events ae
WHERE
    status_id = 1
    AND JSONExtractString(src_endpoint, 'ip') NOT IN (
        SELECT
            ip
        FROM
            authentication_team_ip
        WHERE
            success_rate > 0
    )
    AND toHour(time) NOT IN (
        SELECT
            hour_of_day
        FROM
            authentication_team_hour_of_day
        WHERE
            success_count >= 20
    )
    AND ingested_at > now() - INTERVAL 1 DAY;

​

Null values

Storing null values in Clickhouse can be considered an anti-pattern. Instead, identifiable default values are used. For strings this would be the empty string "". For Int64 columns this would be -12341234.

​

Timestamps

All timestamps are stored and presented in UTC.

​

Resource limits

• 2000 rows per query (100 for custom detections)
• 4GB of memory per query
• 60s of execution time per query