Clean Up Redash Database

README.md

Cleanup Redash Queries (Housekeeping)

Use the following clean-up scripts to help locate redundant queries, widgets, dashboards and alerts within Redash. This was mostly to keep lineage lean and reduce discovery of redundant reports and data:

• locate_expired_assets.sql - Finds expired queries and other assets downstream from that query
• locate_expired_dashboards.sql - Finds unused dashboards older than 1 year
• locate_expired_alerts.sql - Finds unused alerts not executed older than 1 year
• locate_expired_users.sql - Finds unused users not logged older than 90 days
• locate_insecure_tokens.sql - Finds API tokens older than 90 days still in use
• locate_orphaned_connections.sql - Finds orphaned data connections with no queries
• locate_orphaned_visualisations.sql - Finds orphaned visualisations with no widgets
• locate_large_queryresults.sql - Finds top 100 query results saved by size in Mb's
• locate_expired_queryresults.sql - Finds expired query results 90 days, or orphaned user/query.

---

• remove_expired_assets.sql - Deletes expired queries and other assets downstream from that query
• remove_expired_dashboards.sql - Deletes unused dashboards older than 1 year
• remove_expired_alerts.sql - Deletes unused alerts not executed older than 1 year
• remove_expired_users.sql - Deletes unused users not logged older than 90 days
• remove_insecure_tokens.sql - Deletes API tokens older than 90 days still in use

Use at your own risk. The queries above will only show expired content and users, adapt to your needs.

locate_expired_alerts.sql

WITH alert_triggers AS (
  SELECT 
    object_id::int AS alert_id, 
    MAX(created_at) AS last_triggered
  FROM 
    events
  WHERE 
    object_type = 'alert' 
    AND action = 'trigger'
  GROUP BY 
    object_id
)
SELECT 
  a.id, 
  a.name, 
  at.last_triggered,
  a.user_id AS alert_owner_id,
  u.name AS alert_owner_name
FROM 
  alerts a
LEFT JOIN 
  alert_triggers at ON at.alert_id = a.id
LEFT JOIN 
  users u ON u.id = a.user_id
WHERE 
  at.last_triggered < NOW() - INTERVAL '1 year' OR 
  at.last_triggered IS NULL;

locate_expired_assets.sql

WITH dashboard_data AS (
  SELECT 
    d.id AS dashboard_id, 
    d.name AS dashboard_name, 
    d.user_id AS dashboard_owner_id,
    u.name AS dashboard_owner_name,
    w.id AS widget_id, 
    v.id AS visualization_id, 
    q.id AS query_id,
    q.is_archived AS query_archived,
    q.is_draft AS query_draft,
    a.id AS alert_id
  FROM 
    dashboards d
  LEFT JOIN 
    users u ON u.id = d.user_id
  LEFT JOIN 
    widgets w ON w.dashboard_id = d.id
  LEFT JOIN 
    visualizations v ON v.id = w.visualization_id
  LEFT JOIN 
    queries q ON q.id = v.query_id
  LEFT JOIN
    alerts a ON a.query_id = q.id
),
query_executions AS (
  SELECT 
    object_id::int AS query_id, 
    MAX(created_at) AS last_executed
  FROM 
    events
  WHERE 
    object_type = 'query' 
    AND action = 'execute'
  GROUP BY 
    object_id
)
SELECT 
  dd.dashboard_id, 
  dd.dashboard_name, 
  dd.dashboard_owner_id,
  dd.dashboard_owner_name,
  dd.widget_id, 
  dd.visualization_id, 
  dd.query_id, 
  qe.last_executed,
  dd.query_archived,
  dd.query_draft,
  dd.alert_id
FROM 
  dashboard_data dd
LEFT JOIN 
  query_executions qe ON qe.query_id = dd.query_id
WHERE 
  (dd.query_id IS NOT NULL AND 
  ((qe.last_executed IS NULL OR qe.last_executed < NOW() - INTERVAL '366 days') OR 
  (dd.query_id IS NULL AND qe.last_executed < NOW() - INTERVAL '90 days')));

locate_expired_dashboards.sql

WITH dashboard_views AS (
  SELECT 
    object_id::int AS dashboard_id, 
    MAX(created_at) AS last_viewed
  FROM 
    events
  WHERE 
    object_type = 'dashboard' 
    AND action = 'view'
  GROUP BY 
    object_id
)
SELECT 
  d.id, 
  d.name, 
  dv.last_viewed,
  d.user_id AS dashboard_owner_id,
  u.name AS dashboard_owner_name
FROM 
  dashboards d
LEFT JOIN 
  dashboard_views dv ON dv.dashboard_id = d.id
LEFT JOIN 
  users u ON u.id = d.user_id
WHERE 
  dv.last_viewed < NOW() - INTERVAL '1 year' OR 
  dv.last_viewed IS NULL;

locate_expired_queryresults.sql

SELECT 
  qr.id, 
  qr.query_hash, 
  qr.retrieved_at, 
  LENGTH(qr.data) / (1024.0 * 1024.0) AS result_size_mb, 
  q.id AS query_id, 
  q.name AS query_name, 
  u.name AS owner_name,
  CASE
    WHEN q.id IS NULL THEN TRUE
    ELSE FALSE
  END AS is_orphan_query,
  CASE
    WHEN u.id IS NULL THEN TRUE
    ELSE FALSE
  END AS is_orphan_user,
  CASE
    WHEN qr.retrieved_at < NOW() - INTERVAL '90 days' THEN TRUE
    ELSE FALSE
  END AS is_expired
FROM 
  query_results qr
LEFT JOIN 
  queries q ON q.latest_query_data_id = qr.id
LEFT JOIN 
  users u ON u.id = q.user_id
ORDER BY 
  result_size_mb DESC;

locate_expired_users.sql

WITH user_logins AS (
  SELECT 
    user_id, 
    MAX(created_at) AS last_login
  FROM 
    events
  WHERE 
    action = 'login'
  GROUP BY 
    user_id
)
SELECT 
  u.id, 
  u.name, 
  u.email, 
  ul.last_login
FROM 
  users u
LEFT JOIN 
  user_logins ul ON ul.user_id = u.id
WHERE 
  ul.last_login < NOW() - INTERVAL '90 days' OR 
  ul.last_login IS NULL;

locate_insecure_tokens.sql

SELECT 
  ak.id AS api_key_id, 
  u.name AS user_name, 
  u.id AS user_id, 
  ak.created_at AS last_rotated
FROM 
  api_keys ak
JOIN 
  users u ON u.id = ak.object_id
WHERE 
  ak.active = TRUE AND 
  ak.created_at < NOW() - INTERVAL '90 days';

locate_large_queryresults.sql

    SELECT 
  qr.id, 
  qr.query_hash, 
  qr.retrieved_at, 
  LENGTH(qr.data) / (1024.0 * 1024.0) AS result_size_mb, 
  q.id AS query_id, 
  q.name AS query_name, 
  u.name AS owner_name
FROM 
  query_results qr
JOIN 
  queries q ON q.latest_query_data_id = qr.id
JOIN 
  users u ON u.id = q.user_id
ORDER BY 
  result_size_mb DESC
LIMIT 100;

locate_orphaned_connections.sql

SELECT 
  ds.id, 
  ds.name, 
  ds.type, 
  ds.created_at
FROM 
  data_sources ds
LEFT JOIN 
  queries q ON q.data_source_id = ds.id
WHERE 
  q.id IS NULL;

locate_orphaned_visualisations.sql

SELECT 
  v.id, 
  v.query_id, 
  v.created_at
FROM 
  visualizations v
LEFT JOIN 
  widgets w ON w.visualization_id = v.id
WHERE 
  w.id IS NULL;

remove_expired_alerts.sql

WITH alert_triggers AS (
  SELECT 
    object_id::int AS alert_id, 
    MAX(created_at) AS last_triggered
  FROM 
    events
  WHERE 
    object_type = 'alert' 
    AND action = 'trigger'
  GROUP BY 
    object_id
)
DELETE FROM alerts
WHERE id IN (
  SELECT 
    a.id
  FROM 
    alerts a
  LEFT JOIN 
    alert_triggers at ON at.alert_id = a.id
  LEFT JOIN 
    users u ON u.id = a.user_id
  WHERE 
    at.last_triggered < NOW() - INTERVAL '1 year' OR 
    at.last_triggered IS NULL
);

remove_expired_assets.sql

WITH dashboard_data AS (
  SELECT 
    d.id AS dashboard_id, 
    d.name AS dashboard_name, 
    d.user_id AS dashboard_owner_id,
    u.name AS dashboard_owner_name,
    w.id AS widget_id, 
    v.id AS visualization_id, 
    q.id AS query_id,
    q.is_archived AS query_archived,
    q.is_draft AS query_draft,
    a.id AS alert_id
  FROM 
    dashboards d
  LEFT JOIN 
    users u ON u.id = d.user_id
  LEFT JOIN 
    widgets w ON w.dashboard_id = d.id
  LEFT JOIN 
    visualizations v ON v.id = w.visualization_id
  LEFT JOIN 
    queries q ON q.id = v.query_id
  LEFT JOIN
    alerts a ON a.query_id = q.id
),
query_executions AS (
  SELECT 
    object_id::int AS query_id, 
    MAX(created_at) AS last_executed
  FROM 
    events
  WHERE 
    object_type = 'query' 
    AND action = 'execute'
  GROUP BY 
    object_id
)
DELETE FROM queries
WHERE id IN (
  SELECT 
    dd.query_id
  FROM 
    dashboard_data dd
  LEFT JOIN 
    query_executions qe ON qe.query_id = dd.query_id
  WHERE 
    dd.query_id IS NOT NULL AND 
    (
      qe.last_executed IS NULL OR 
      qe.last_executed < NOW() - INTERVAL '366 days' OR 
      (
        dd.query_id IS NULL AND 
        qe.last_executed < NOW() - INTERVAL '90 days'
      )
    )
);

remove_expired_dashboards.sql

WITH dashboard_views AS (
  SELECT 
    object_id::int AS dashboard_id, 
    MAX(created_at) AS last_viewed
  FROM 
    events
  WHERE 
    object_type = 'dashboard' 
    AND action = 'view'
  GROUP BY 
    object_id
)
DELETE FROM dashboards
WHERE id IN (
  SELECT 
    d.id
  FROM 
    dashboards d
  LEFT JOIN 
    dashboard_views dv ON dv.dashboard_id = d.id
  WHERE 
    dv.last_viewed < NOW() - INTERVAL '1 year' OR 
    dv.last_viewed IS NULL
);

remove_expired_users.sql

WITH user_logins AS (
  SELECT 
    user_id, 
    MAX(created_at) AS last_login
  FROM 
    events
  WHERE 
    action = 'login'
  GROUP BY 
    user_id
)
DELETE FROM users
WHERE id IN (
  SELECT 
    u.id
  FROM 
    users u
  LEFT JOIN 
    user_logins ul ON ul.user_id = u.id
  WHERE 
    ul.last_login < NOW() - INTERVAL '90 days' OR 
    ul.last_login IS NULL
);

remove_insecure_tokens.sql

DELETE FROM api_keys
WHERE id IN (
  SELECT 
    ak.id 
  FROM 
    api_keys ak
  JOIN 
    users u ON u.id = ak.object_id
  WHERE 
    ak.active = TRUE AND 
    ak.created_at < NOW() - INTERVAL '90 days'
);