Advanced Queries

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;

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;

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;