Auth Sessions Exploration Queries

Queries to analyze authentication sessions in Farfalla.

Sessions by Entry Point

SELECT
  auth_entry_point,
  COUNT(*) AS session_count
FROM auth_sessions
GROUP BY auth_entry_point
ORDER BY session_count DESC;

Bot Sessions Count

SELECT
  COUNT(*) AS bot_sessions
FROM auth_sessions
WHERE LOWER(user_agent) REGEXP 'bot|crawl|spider|slurp';

Sessions by Tenant (Top 50)

SELECT
  COALESCE(tenant_id, 0) AS tenant_id,
  COUNT(*) AS sessions
FROM auth_sessions
GROUP BY tenant_id
ORDER BY sessions DESC
LIMIT 50;

Active Sessions (Last Hour)

-- e.g. last 60 minutes
SELECT
  COUNT(*) AS active_last_hour
FROM auth_sessions
WHERE last_activity >= UNIX_TIMESTAMP() - 60 * 60;

Top User Agents

SELECT
  SUBSTRING_INDEX(user_agent, ' ', 1) AS ua_prefix,
  COUNT(*) AS cnt
FROM auth_sessions
GROUP BY ua_prefix
ORDER BY cnt DESC
LIMIT 10;

Sessions by Tenant and Entry Point

SELECT
  tenant_id,
  auth_entry_point,
  COUNT(*) AS cnt
FROM auth_sessions
GROUP BY tenant_id, auth_entry_point
ORDER BY tenant_id, cnt DESC;