Metabase Metadata SQL


#1

Metabase is lacking in some of the management information I’d like to see. Hopefully the queries below will help a few others build up a management dashboard too.
In summary:

  1. List of Dashboards
  2. List of Questions
  3. List of Questions in each Dashboard
  4. List of Questions not in any Dashboard
  5. List of Users
  6. LIst of Users who have never logged in
  7. List of Dashboards viewed by each user and count of views
  8. Pretty much anything else you can dream up
/* All dashboards */
SELECT rd.id, rd.name, rd.description, rd.archived FROM report_dashboard rd;
/* All questions */
select q.id, q.name, q.description, q.archived, q.display, q.query_type
from report_card q;
/* Current dashboards and their questions */
SELECT rd.id, rd.name as DashboardName, rd.description as DashboardQuestion, q.name as QuestionName, q.description as QuestionDescription, q.display as QuestionDisplay
FROM report_dashboard rd
INNER JOIN report_dashboardcard rdc on rd.id = rdc.dashboard_id
INNER JOIN report_card q on q.id = rdc.card_id
where rd.archived = 0 and q.archived = 0;
/* Questions without a dashboard */
select q.id, q.name, q.description, q.display, q.query_type
from report_card q
LEFT OUTER JOIN report_dashboardcard rdc on rdc.card_id = q.id
where q.archived = 0 and rdc.card_id is null;
/* users who have never logged in */
select id, email, first_name, last_name
from core_user
where last_login is null;
/* which dashboards has a user viewed */
select count(cu.id) as viewCount, cu.email, cu.first_name, cu.last_name, rd.name as DashboardName, rd.description as DashboardDescription
from core_user cu
INNER JOIN view_log vl on vl.user_id = cu.id
INNER JOIN report_dashboard rd on rd.id	= vl.model_id
where vl.model='dashboard'
GROUP BY cu.email, cu.first_name, cu.last_name, rd.name, rd.description

Application and user activities logging
Analytics of Metabase
Question last viewed at [SOLVED]
#2

In addition:

SELECT * FROM metabase.report_card where dataset_query like '%native%'
gives you all hand written questions in metabase with name and description


#3

How do you get access to the report_dashboardcard table? Is it possible to get the information on how your users are interacting with Metabase?


#4

There’s only information on what they’re viewing, not how they interact.


#5

Do I need to be using an analytics tool in order to create a table to store what people are viewing? Or is there a table in my own database that I can connect to?


#6

It’s not in your application database (metabase doesn’t write back). It’s in Metabase’s own database.


#7

Very helpful.

In addition to this, you can connect Metabase to Metabase’s own database. And then store the query’s by OP as native queries. :slight_smile: This is inception or rather meta-metabase. :slight_smile: