Advanced Queries

​

Quick Start

​

Schema

​

Tables

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

​

Views

​

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
• authentication_email_vpn - Detects common VPN usage 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
• authentication_team_vpn - Analyzes common VPN usage 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

​

Timestamps

​

Resource limits

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