> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirespeed.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Advanced Queries

> Advanced queries and filtering

Use [ClickHouse](https://clickhouse.com/) SQL to investigate the normalized SIEM data produced by your integrations. Wirespeed stores common event fields in typed `wspd_*` tables and retains the original vendor payload in its source-specific `*_logs` table.

<Tip>Start with a normalized table for cross-source investigations, then query the source table named in `_wspd_table` when you need vendor-specific fields.</Tip>

## Quick Start

To run an advanced query, navigate to the **Events** page and select the <Icon icon="code" size="20" /> icon in the top right.

Select **Ask Wirespeed** beside the SQL editor to start the **Create Advanced Query** workflow. Ask Wirespeed inspects your connected tables, verifies the selected fields, runs a bounded representative query, validates the final SQL, and returns an **Execute** button that opens the query in Advanced Events.

## Schema

Always bound event queries with `_wspd_time`. The Advanced page supplies `{startTime: DateTime64}` and `{endTime: DateTime64}` from its timeframe selector.

### Tables

* `wspd_events` — consolidated event index across connected log sources
* `wspd_authentication_events` — normalized login and authentication activity
* `wspd_productivity_events` — normalized email, file, and collaboration activity
* `wspd_dns_events` — normalized DNS activity
* `*_logs` — source-specific raw log tables, such as `microsoft_entra_signin_logs`

The consolidated `wspd_events` table includes:

| Column                   | Description                                             |
| ------------------------ | ------------------------------------------------------- |
| `_wspd_id`               | Event identifier                                        |
| `_wspd_team_id`          | Owning team; automatically scoped by row-level security |
| `_wspd_integration_id`   | Source integration identifier                           |
| `_wspd_integration_slug` | Source integration type                                 |
| `_wspd_table`            | Source table containing the vendor payload              |
| `_wspd_event_type`       | Normalized event type                                   |
| `_wspd_time`             | Event timestamp in UTC                                  |
| `_wspd_ingested_at`      | Time Wirespeed stored the event                         |
| `_wspd_observables`      | Normalized searchable values                            |

<Expandable title="wspd_authentication_events fields">
  | Column              | Description                                              |
  | ------------------- | -------------------------------------------------------- |
  | `status`            | Authentication result, including `SUCCESS` and `FAILURE` |
  | `is_mfa`            | Whether the event involved MFA                           |
  | `actor_email`       | Authenticating user's email                              |
  | `actor_username`    | Authenticating username                                  |
  | `src_ip`            | Source IP address                                        |
  | `src_ip_country`    | Enriched source country                                  |
  | `src_ip_asn_number` | Enriched source ASN                                      |
  | `src_ip_asn_name`   | Enriched source network name                             |
  | `src_ip_vpn`        | VPN indicator                                            |
  | `src_ip_proxy`      | Proxy indicator                                          |
  | `src_ip_tor`        | Tor indicator                                            |
  | `src_ip_relay`      | Relay indicator                                          |
  | `user_agent`        | Source user agent                                        |
  | `session_uid`       | Authentication session identifier                        |
</Expandable>

### Examples

<Expandable title="Count events by normalized type">
  ```sql theme={null}
  SELECT
      _wspd_event_type,
      count(*) AS event_count
  FROM wspd_events
  WHERE _wspd_time >= {startTime: DateTime64}
      AND _wspd_time < {endTime: DateTime64}
  GROUP BY _wspd_event_type
  ORDER BY event_count DESC;
  ```
</Expandable>

<Expandable title="Find failed authentication activity">
  ```sql theme={null}
  SELECT
      _wspd_time,
      actor_email,
      src_ip,
      src_ip_country,
      user_agent,
      message
  FROM wspd_authentication_events
  WHERE status = 'FAILURE'
      AND _wspd_time >= {startTime: DateTime64}
      AND _wspd_time < {endTime: DateTime64}
  ORDER BY _wspd_time DESC
  LIMIT 100;
  ```
</Expandable>

<Expandable title="Find successful logins from countries not seen in the preceding 30 days">
  ```sql theme={null}
  WITH historical_locations AS (
      SELECT DISTINCT
          lower(actor_email) AS actor_email,
          upper(src_ip_country) AS country
      FROM wspd_authentication_events
      WHERE status = 'SUCCESS'
          AND src_ip_country != ''
          AND _wspd_time >= toDateTime({startTime: DateTime64}) - INTERVAL 30 DAY
          AND _wspd_time < {startTime: DateTime64}
  )
  SELECT
      current._wspd_time,
      current.actor_email,
      current.src_ip,
      current.src_ip_country,
      current.user_agent
  FROM wspd_authentication_events AS current
  WHERE
      current.status = 'SUCCESS'
      AND current.src_ip_country != ''
      AND current._wspd_time >= {startTime: DateTime64}
      AND current._wspd_time < {endTime: DateTime64}
      AND (lower(current.actor_email), upper(current.src_ip_country)) NOT IN (
          SELECT actor_email, country
          FROM historical_locations
      )
  ORDER BY current._wspd_time DESC
  LIMIT 100;
  ```
</Expandable>

### Null values

Most normalized fields use non-nullable types. Missing strings are represented by `''`, while missing numeric values use the column's default.

### Timestamps

All timestamps are stored and presented in UTC.

## Resource limits

* **2000** rows per interactive query
* **4GB** of memory per query
* **60s** of execution time per query

[Custom detections](/events/custom-detections) are created and verified in Ask Wirespeed with a limit of fewer than 100 matches in the last hour and a 7.5-second execution ceiling.