Tracking Metabase Usage

[gilhs]

As we've rolled out Metabase across our organization, we want to start tracking actual usage - who uses it, on what basis, what dashboards/cards are they using, etc.

I've found a (relatively) older PR (#2386) that discusses adding query metadata as a comment to the queries - this is definitely a good solution that we can take advantage of to answer our questions; however, I'm not seeing the metadata appended to our queries.

We're using Google BigQuery, and the GCP Stackdriver integration to log all queries run through BQ.
Is this not supported for BQ? Or am I missing a configuration to control this somewhere?

If not - what are possibilities do we have of tracking usage?

[robinfrick]

You can connect to Metabases own Database. Actually we have added it to Metabase itself with a read only user and made it only available to Admins (we don't have a read replica and don't want someone to accidentally run very expensive queries on it).

Here's a first inspiration for getting the 10 most active users for question creation and saving changes:

SELECT first_name || ' ' || last_name as name, 
        SUM(CASE WHEN topic = 'card-create' THEN 1 END) as "question created", 
        SUM(CASE WHEN topic = 'card-update' THEN 1 END) as "question updated"
FROM Activity INNER JOIN core_user on core_user.id = Activity.user_id
GROUP BY 1
ORDER BY 2 desc , 3 desc LIMIT 10

[gilhs]

Thanks, I'll check it out!

[salsakran]

@gilhs As @robinfrick mentioned, the application database is JustAnotherDatabase you can attach Metabase to and pull out whatever reports you need.

Depending on your scale of usage, we're also working on a dedicated "analytics on analytics" product on top of Metabase internally, and would love some feedback if you're open to a conversation.

[gilhs]

Always open to conversation :)

In terms of our needs - our scale is currently small, and it looks like the data that's currently being saved in the application DB is just right for what we're looking for. What kind of advanced analytics are you developing?

[vijayvenkatesh]

Hi @robinfrick @salsakran Got the local metabase connected and added to in metabase.
When I run the query above as myself (admin) I get
Running SQL queries against H2 databases using the default (admin) database user is forbidden.

[ans-4175]

@vijayvenkatesh I think you need to migrate metabase to another sql dbms and then connect metabase against your newly migrated db

[philippkeller]

After migrating from H2 to Postgres (which is best practice anyway for production use) I was able to query the meta data. I expanded the query from @robinfrick above to also show the number of queries done (we promote metabase as an ad-hoc tool so I don't expect many people are saving their questions) and limiting to the past 30 days:

SELECT *, (select count(*) from query_execution where executor_id=card.id and started_at > current_date - 30) as "queries executed" FROM 
(SELECT core_user.id as id, first_name || ' ' || last_name as name, 
        SUM(CASE WHEN topic = 'card-create' AND timestamp > current_date - 30 THEN 1 END) as "question created", 
        SUM(CASE WHEN topic = 'card-update' AND timestamp > current_date - 30 THEN 1 END) as "question updated"
FROM Activity INNER JOIN core_user on core_user.id = Activity.user_id
GROUP BY core_user.id) card
ORDER BY "queries executed" DESC

[ambarshante]

@robinfrick and @philippkeller thanks – this works great. Just one small issue – the credentials to the various databases connected to Metabase are exposed in the metabase_database table. Is there a way to not sync this table, or to store these in a hashed form?

[robinfrick]

@ambarshante if you set up a dedicated reader for your Metabase database queries through Metabase (as in not the DB user that metabase uses itself in the backend) then you can just revoke select permission for the table where the credentials are stored. Or probably better only selectively grant select for the tables you actually want to access for analytical purposes.

[flamber]

@ambarshante You should be encrypting your database details:
https://www.metabase.com/docs/latest/operations-guide/encrypting-database-details-at-rest.html

[vitorhirota]

We also use questions against metabase metadata db to list, for instance, most executed cards.

But I would like to know what are the most executed dashboards.

I saw there's a dashboard_id field in query_execution, but it is always empty.

Not sure if it's something I'm missing configuring, or it should be a separate issue.

[flamber]

@vitorhirota I think it might deserve it's own issue. Would you create one? If you use Public dashboards, then it includes the dashboard_id in query_execution, so perhaps it's just a missing parameter for regular dashboards.

[flamber]

Closing this, since the Enterprise Edition has Audit functionality: https://www.metabase.com/docs/latest/enterprise-guide/audit.html