Monitor queries

[Auric-Manteo]

How can I monitor queries, specifically I would like to see:

• failing queries so I can fix them
• queries that take a long time so I can fix them if necessary
• queries that no one has used in x days so I can archive/improve/remove them
• queries that are used/viewed often so I can share the buzz and make the most out of them

[arikfr]

For failing scheduled queries you can enable the email report feature (check general settings). It will email you when scheduled queries fail.

For the other things you mentioned, you can add Redash's own metadata database as a data source and build some queries to fetch this information. Ideally we would add it in the UI, but this will take time, and until then it will be great to start a collaborative thread on example queries on the metadata database.

[Auric-Manteo]

Great idea, thanks!

[Auric-Manteo]

After I thought about this a bit I realized that this would expose sensitive data like password hashes, so it is not a good solution.
I tried to retrieve the data from the API instead, which does not seem to be possible either.
A somewhat cumbersome workaround would be to ingest the data into another database source and transform it to filter out sensitive data.
Do you have an overview of the data model so I can quickly see which tables I should keep or start with and what I should filter out?

[Auric-Manteo]

For anyone else that might be interested we made this work by restricting the access to the internal database source to a specific user group. That way the sensitive data is only exposed to specific users.
Here are a few of the queries that were useful for monitoring if you need a sense of the internal schema:

Usage:

  SELECT
      TO_CHAR(e.created_at, 'YYYY-MM') AS month,
      COUNT(CASE WHEN e.object_type = 'dashboard' AND e.action = 'view' THEN 1 END) AS dashboard_views,
      COUNT(CASE WHEN e.object_type = 'query' AND e.action = 'execute' THEN 1 END) AS query_executions,
      COUNT(CASE WHEN e.object_type = 'query' AND e.action IN ('view', 'view_source') THEN 1 END) AS query_views,
      COUNT(*) AS total_activities,
      COUNT(DISTINCT e.user_id) AS unique_users
  FROM events e
  WHERE e.object_type IN ('dashboard', 'query')
      AND e.object_id IS NOT NULL
      AND e.object_id != ''
      AND e.created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
  GROUP BY TO_CHAR(e.created_at, 'YYYY-MM')
  ORDER BY month DESC;

Valuable dashboards:

WITH latest_dashboard_attributes AS (
  SELECT DISTINCT
      d.id,
      FIRST_VALUE(d.name) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS name,
      FIRST_VALUE(d.slug) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS slug,
      FIRST_VALUE(d.is_archived) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS is_archived,
      FIRST_VALUE(d.is_draft) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS is_draft,
      FIRST_VALUE(d.tags) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS tags,
      FIRST_VALUE(d.user_id) OVER (PARTITION BY d.id ORDER BY d.id ASC) AS original_author_id
  FROM dashboards d
)
SELECT
  lda.name AS dashboard_name,
  'https://bi.data.flowdev.se/dashboards/' || e.object_id || '-' || lda.slug as dashboard_link,
  lda.tags,
  COUNT(*) AS usage_count,
  MAX(e.created_at) AS last_accessed,
  STRING_AGG(DISTINCT u.name, ', ' ORDER BY u.name) AS used_by
FROM events e
JOIN latest_dashboard_attributes lda ON e.object_id::integer = lda.id
LEFT JOIN users u ON e.user_id = u.id
WHERE e.object_type = 'dashboard'
  AND e.object_id IS NOT NULL
  AND e.object_id != ''
  AND u.name IS NOT NULL
  AND lda.is_draft = false
  AND lda.is_archived = false
GROUP BY
  e.object_id,
  lda.name,
  lda.slug,
  lda.is_archived,
  lda.is_draft,
  lda.tags
HAVING MAX(e.created_at) > DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1.2 months')
ORDER BY usage_count DESC;

Queries:

  WITH latest_query_attributes AS (
      SELECT DISTINCT
          q.id,
          FIRST_VALUE(q.name) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS name,
          FIRST_VALUE(q.is_archived) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS is_archived,
          FIRST_VALUE(q.is_draft) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS is_draft,
          FIRST_VALUE(q.tags) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS tags,
          FIRST_VALUE(q.schedule) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS schedule,
          FIRST_VALUE(q.schedule_failures) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS schedule_failures,
          FIRST_VALUE(q.user_id) OVER (PARTITION BY q.id ORDER BY q.id ASC) AS original_author_id,
          FIRST_VALUE(q.last_modified_by_id) OVER (PARTITION BY q.id ORDER BY q.id DESC) AS last_modified_by_id
      FROM queries q
  )
  SELECT
      u.name AS original_author,
      lqa.name,
      'https://bi.data.flowdev.se/query/' || e.object_id as query_link,
      lqa.tags,
      lqa.schedule,
      lqa.schedule_failures,
      last_author.name as last_change_author,
      COUNT(*) AS usage_count,
      MAX(e.created_at) AS last_accessed
  FROM events e
  JOIN latest_query_attributes lqa ON e.object_id::integer = lqa.id
  LEFT JOIN users u ON lqa.original_author_id = u.id
  LEFT JOIN users last_author ON lqa.last_modified_by_id = last_author.id
  WHERE e.object_type = 'query'
      AND e.object_id IS NOT NULL
      AND e.object_id != ''
      AND lqa.is_archived = false
      AND lqa.is_draft = false
  GROUP BY
      e.object_id,
      u.name,
      lqa.name,
      lqa.is_archived,
      lqa.is_draft,
      lqa.tags,
      lqa.schedule,
      lqa.schedule_failures,
      last_author.name
  HAVING MAX(e.created_at) < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months')
  ORDER BY last_accessed DESC;

Dashboards:

  WITH latest_dashboard_attributes AS (
      SELECT DISTINCT
          d.id,
          FIRST_VALUE(d.name) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS name,
          FIRST_VALUE(d.slug) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS slug,
          FIRST_VALUE(d.is_archived) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS is_archived,
          FIRST_VALUE(d.is_draft) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS is_draft,
          FIRST_VALUE(d.tags) OVER (PARTITION BY d.id ORDER BY d.id DESC) AS tags,
          FIRST_VALUE(d.user_id) OVER (PARTITION BY d.id ORDER BY d.id ASC) AS original_author_id
      FROM dashboards d
  )
  SELECT
      lda.name AS dashboard_name,
      'https://bi.data.flowdev.se/dashboards/' || e.object_id || '-' || lda.slug as dashboard_link,
      lda.tags,
      COUNT(*) AS usage_count,
      MAX(e.created_at) AS last_accessed,
      STRING_AGG(distinct u.name,',') AS used_by
  FROM events e
  JOIN latest_dashboard_attributes lda ON e.object_id::integer = lda.id
  LEFT JOIN users u ON e.user_id = u.id
  WHERE e.object_type = 'dashboard'
      AND e.object_id IS NOT NULL
      AND e.object_id != ''
      AND u.name IS NOT NULL
      AND is_archived = false
      AND is_draft = false
  GROUP BY
      e.object_id,
      u.name,
      lda.name,
      lda.slug,
      lda.is_archived,
      lda.is_draft,
      lda.tags
  ORDER BY usage_count DESC;

User stats:

  SELECT
      TO_CHAR(e.created_at, 'YYYY-MM') AS month,
      u.name AS user_name,
      u.created_at AS user_joined,
      COUNT(CASE WHEN e.object_type = 'query' AND e.action = 'execute' THEN 1 END) AS query_executions,
      COUNT(CASE WHEN e.object_type = 'query' AND e.action IN ('view', 'view_source') THEN 1 END) AS query_views,
      COUNT(CASE WHEN e.object_type = 'query' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN 1 END) AS query_edits,
      COUNT(CASE WHEN e.object_type = 'dashboard' AND e.action = 'view' THEN 1 END) AS dashboard_views,
      COUNT(CASE WHEN e.object_type = 'dashboard' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN 1 END) AS dashboard_edits,
      COUNT(e.id) AS total_activities,
      MAX(CASE WHEN e.object_type = 'query' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN e.created_at END) AS last_query_edit,
      MAX(CASE WHEN e.object_type = 'dashboard' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN e.created_at END) AS last_dashboard_edit,
      COUNT(DISTINCT CASE WHEN e.object_type = 'query' THEN e.object_id END) AS unique_queries,
      COUNT(DISTINCT CASE WHEN e.object_type = 'dashboard' THEN e.object_id END) AS unique_dashboards,
      COUNT(DISTINCT CASE WHEN e.object_type = 'query' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN e.object_id END) AS unique_queries_edited,
      COUNT(DISTINCT CASE WHEN e.object_type = 'dashboard' AND e.action IN ('edit', 'edit_name', 'edit_description') THEN e.object_id END) AS unique_dashboards_edited
  FROM users u
  JOIN events e ON u.id = e.user_id
      AND e.object_type IN ('query', 'dashboard')
  WHERE u.name IS NOT NULL
  GROUP BY TO_CHAR(e.created_at, 'YYYY-MM'), u.name, u.created_at
  ORDER BY TO_CHAR(e.created_at, 'YYYY-MM') DESC, total_activities DESC;

I hope this is helpful and I think it would be really valuable to add dashboards based on this as a feature.