Metabase Metadata SQL

[AndrewMBaines]

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

[EvaS]

In addition:

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

[Yes]

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?

[AndrewMBaines]

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

[Yes]

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?

[AndrewMBaines]

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

[joboyx]

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. This is inception or rather meta-metabase.

[MetaKC]

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?

[flamber]

@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

[louisguitton]

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

[marco]

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'

[renaud]

Most viewed dasboards in the last 3 months

    /* Most viewed dasboards in the last 3 months */
select rd.name as DashboardName, count(cu.id) as viewCount, 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' 
-- last 3 months
AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d') 
   BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
   AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')

GROUP BY rd.name, rd.description
ORDER BY viewCount DESC

[renaud]

Most viewed questions in the last 3 months

/* Most viewed questions in the last 3 months */
SELECT 
  rc.name AS CardName
, COUNT(vl.user_id) AS viewCount
, rc.description AS CardDescription
FROM view_log vl
INNER JOIN report_card rc ON rc.id = vl.model_id
WHERE vl.model='card' 
-- last 3 months
AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d') 
   BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
   AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')

GROUP BY rc.name, rc.description
ORDER BY viewCount DESC

Questions never viewed in the last 3 months

/* Questions never viewed in the last 3 months */
SELECT 
  rc.id
, CONCAT('http://your_base_url', rc.id) AS CardUrl
, rc.name AS CardName
, rc.description AS CardDescription
FROM report_card rc
WHERE rc.id NOT IN (
    SELECT vl.model_id
    FROM view_log vl 
    WHERE vl.model='card' 
    -- last 3 months
    AND str_to_date(concat(date_format(vl.timestamp, '%Y-%m'), '-01'), '%Y-%m-%d') 
       BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -3 month), '%Y-%m'), '-01'), '%Y-%m-%d')
       AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')
)

[brian]

What format is the dataset query stored in the app DB, and what is the best way to convert/clean after exporting / copying dataset query to obtain pure SQL? @marco

[ThaoDinh]

@AndrewMBaines hello, very useful queries, but could you show me where I can run those? of course i cant run in metabase with my db

[AndrewMBaines]

I just run them from Metabase. Your database should be either Postgres or MySQL. If you're still on H2, you need to migrate now before you lose all your work.

[ThaoDinh]

My application database has been Postgres already, but I still could not run those queries. May it be due to that my Metabase is not paid-plan (I mean Pro/Enterprise)?

[AndrewMBaines]

Nope. Should work on any plan. You just need the details of the database.

[ThaoDinh]

Hmm, could you pls show me how to add Metabase’s own application database to Metabase as a datasource? Or any related docs, tks.

[flamber]

@ThaoDinh Go to Admin > Databases and create a new data source, where you input the credentials to your Metabase application database.