Metabase Metadata SQL

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
12 Likes

In addition:

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

7 Likes

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?

2 Likes

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

2 Likes

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?

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

1 Like

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:

2 Likes

This was very helpful! I was able to create a “Top 10 Dashboard” and “Top 10 Question” set of questions going over a recent period of time.

I’d like to be able to find any dashboard associated with a specific question. I have built the query, but in order to use a field filter, I believe I need to go in as an administrator and change the “Type” of the dashboard name field. My questions is…if I’m connecting directly to the Metabase Database, from Metabase, and changing field types (from name to" Category")…does that cause any problems with the Metabase site? Changing the type of a field that is used everywhere in the system seems dangerous. Should I leave this alone? :slight_smile:

@MetaKC When you add Metabase’s own application database to Metabase as a datasource, then the Data Model only has something to do with the datasource - and not the application database itself, that Metabase writes information to.
So you can change anything in the Data Model.
But as always, make sure to have backups, and if you’re using the default H2 database, then you should migrate away:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

Thanks for this !
Adding the 2 models I use at the minute:

-- timeseries of cumulated new users
with data as (
    select 
        timestamp :: date as date_at,
        count(user_id) as count
    from activity
    where topic= 'user-joined'
    group by 1
)
select
  date_at,
  sum(count) over (order by date_at asc rows between unbounded preceding and current row) as running_users
from data
-- weeky active users
select
    date_trunc('week', created_at) as week_at,
    count(distinct user_id) as weekly_active_users
from
    core_session
group by 1
order by 1 asc
1 Like

I’m adding a few queries that are really useful to me and maybe can be helpful to someone else.
I use them after changing the data model that feeds 100s/1000s of queries and I’m absolutely not even considering changing anything by hand!

A) replace anything with a regex within the actual code of my queries

Step 1: identify all the queries contain the term I need to update

SELECT 
    dataset_query,
    *
FROM report_card 
where dataset_query like '%native%'	-- to isolate those in native SQL
and dataset_query ~* 'field\/edit\/\?id=.[\|]{2}your_old_value'

Step 2: update the queries

UPDATE
	report_card
SET 
	dataset_query = REGEXP_REPLACE
	(
	dataset_query,				
	'field\/edit\/\?id=.[\|]{2}your_old_value',
	'field/edit/?id='||'||your_new_value',
	'g'									
	)	
WHERE 
		dataset_query like '%native%'			
		AND dataset_query ~* 'field\/edit\/\?id=.[\|]{2}field_id'

After Step 2 I usually run Step 1 to confirm I have successfully updated everything and then check that all my dashboards are running smoothly

B) Update filters that where once based on my old column_name

Step 1: I find all the filters that rely on my old column_name

SELECT 
* 
FROM public.metabase_field 
WHERE display_name = 'old_column_name'
    OR name = 'old_column_name'
ORDER BY created_at DESC

Step 2: update from old to new column_name ( I usually do it one at the time to double check everything)

UPDATE metabase_field
SET display_name = 'new_column_name'
WHERE display_name = 'old_column_name'