SELECT event.category, count(*) AS total
FROM events
GROUP BY event.category
ORDER BY total DESC

SELECT date_trunc('hour', timestamp) AS hour,
       count(*) AS events,
       approx_distinct(user.name) AS users
FROM events
GROUP BY 1
ORDER BY 1

SELECT "timestamp", "host.name", "process.name", "process.executable"
FROM events
WHERE lower("process.name") = 'powershell.exe'
ORDER BY "timestamp" DESC

SELECT "source.ip", count(*) AS attempts,
       approx_distinct("user.name") AS targeted_users
FROM events
WHERE "event.category" = 'authentication'
  AND "event.outcome" = 'failure'
  AND nullif("source.ip", '') IS NOT NULL
GROUP BY "source.ip"
HAVING count(*) >= 5
ORDER BY attempts DESC

SELECT entities.display_name, entities.department
FROM entities
WHERE entities.department IS NOT NULL

SELECT "timestamp", "log.source", "event.original"
FROM events
ORDER BY "timestamp" DESC

SELECT "timestamp", "log.source", "event.original"
FROM events
WHERE strpos(lower("event.original"), 'powershell') > 0
ORDER BY "timestamp" DESC

SELECT "timestamp", json_extract_scalar("event.original", '$.host') AS host,
       json_extract_scalar(json_extract_scalar("event.original", '$.message'), '$.Event.EventData.TargetUserName') AS target_user,
       json_extract_scalar(json_extract_scalar("event.original", '$.message'), '$.Event.EventData.IpAddress') AS source_ip
FROM events
WHERE "event.original" LIKE '%windows_security%'
  AND "event.original" LIKE '%4625%'
  AND try(json_extract_scalar("event.original", '$.log_source')) = 'windows_security'
  AND try(json_extract_scalar(json_extract_scalar("event.original", '$.message'), '$.Event.System.EventID')) = '4625'
ORDER BY "timestamp" DESC

SELECT "timestamp", json_extract_scalar("event.original", '$.host') AS host,
       regexp_extract(json_extract_scalar("event.original", '$.message'), 'a0="([^"]+)"', 1) AS executable,
       json_extract_scalar("event.original", '$.message') AS audit_line
FROM events
WHERE "event.original" LIKE '%linux_auditd%'
  AND try(json_extract_scalar("event.original", '$.log_source')) = 'linux_auditd'
  AND regexp_like(try(json_extract_scalar("event.original", '$.message')), 'type=EXECVE')
ORDER BY "timestamp" DESC

SELECT "timestamp", json_extract_scalar("event.original", '$.userPrincipalName') AS user_email,
       json_extract_scalar("event.original", '$.ipAddress') AS source_ip,
       json_extract_scalar("event.original", '$.status.errorCode') AS error_code,
       json_extract_scalar("event.original", '$.status.failureReason') AS failure_reason
FROM events
WHERE "event.original" LIKE '%entra_signin%'
  AND try(json_extract_scalar("event.original", '$.log_source')) = 'entra_signin'
  AND try_cast(try(json_extract_scalar("event.original", '$.status.errorCode')) AS bigint) > 0
ORDER BY "timestamp" DESC

SELECT coalesce(try(json_extract_scalar("event.original", '$.log_source')), 'unknown') AS raw_source,
       count(*) AS hits
FROM events
WHERE strpos(lower("event.original"), 'powershell') > 0
GROUP BY 1
ORDER BY hits DESC

SELECT indicator_type, threat_type, count(*) AS total
FROM ti
WHERE revoked = false
GROUP BY indicator_type, threat_type
ORDER BY total DESC

SELECT indicator_type, threat_type, count(*) AS total, max(score) AS max_score
FROM ti
WHERE lower(description) LIKE '%brandefense%'
GROUP BY indicator_type, threat_type
ORDER BY total DESC

SELECT observable_value, indicator_type, threat_type, score, confidence, valid_until, revoked,
       substr(description, 1, 200) AS description
FROM ti
WHERE observable_value = '203.0.113.10'
   OR pattern LIKE '%203.0.113.10%'
ORDER BY created_at DESC

SELECT observable_value, indicator_type, threat_type, score, confidence, valid_until
FROM ti
WHERE revoked = false
  AND substr(valid_until, 1, 19) >= date_format(now(), '%Y-%m-%dT%H:%i:%s')
ORDER BY score DESC

WITH ioc AS (
  SELECT observable_value, max(score) AS score, min(threat_type) AS threat_type
  FROM ti
  WHERE indicator_type IN ('IPv4-Addr', 'IPv6-Addr') AND revoked = false
  GROUP BY observable_value
)
SELECT events."source.ip" AS ioc_ip, ioc.threat_type, ioc.score, events."event.action" AS action,
       count(*) AS hits, min(events."timestamp") AS first_seen, max(events."timestamp") AS last_seen
FROM events
JOIN ioc ON events."source.ip" = ioc.observable_value
GROUP BY events."source.ip", ioc.threat_type, ioc.score, events."event.action"
ORDER BY hits DESC

WITH ioc AS (
  SELECT observable_value, max(score) AS score, min(threat_type) AS threat_type
  FROM ti
  WHERE indicator_type IN ('IPv4-Addr', 'IPv6-Addr') AND revoked = false
  GROUP BY observable_value
)
SELECT events."destination.ip" AS ioc_ip, ioc.threat_type, ioc.score,
       count(DISTINCT events."source.ip") AS internal_clients,
       array_agg(DISTINCT events."event.action") AS actions,
       count(*) AS hits
FROM events
JOIN ioc ON events."destination.ip" = ioc.observable_value
GROUP BY events."destination.ip", ioc.threat_type, ioc.score
ORDER BY hits DESC

WITH ioc AS (
  SELECT lower(observable_value) AS domain, max(score) AS score, min(threat_type) AS threat_type
  FROM ti
  WHERE indicator_type = 'Domain-Name' AND revoked = false
  GROUP BY lower(observable_value)
)
SELECT events."dns.question.name" AS domain, ioc.threat_type, ioc.score,
       count(*) AS hits, approx_distinct(events."source.ip") AS clients
FROM events
JOIN ioc ON lower(events."dns.question.name") = ioc.domain
GROUP BY events."dns.question.name", ioc.threat_type, ioc.score
ORDER BY hits DESC

WITH ioc AS (
  SELECT lower(observable_value) AS hash, max(score) AS score, min(malware_family) AS malware_family
  FROM ti
  WHERE indicator_type = 'StixFile' AND revoked = false AND length(observable_value) = 64
  GROUP BY lower(observable_value)
)
SELECT events."timestamp", events."host.name", events."file.name", events."file.hash.sha256",
       ioc.score, ioc.malware_family
FROM events
JOIN ioc ON lower(events."file.hash.sha256") = ioc.hash
ORDER BY events."timestamp" DESC