Creating Looker Usage and Metadata Reports

On this Page
Docs Menu

Admin users have access to an instance’s Usage panel, which connects directly to Looker’s underlying application database, called i__looker. The i__looker database stores information about your Looker instance, including all Looks and dashboards saved on your instance, user information, and 90 days of historical queries:

This page describes the most commonly-used Explores in i__looker, and gives some examples for common uses. This functionality can be useful for auditing and monitoring purposes. The i__looker data is backed up daily, as discussed on this page.

The History Explore

The History Explore includes information about each query run on your Looker instance in the last 90 days.

You can access the History Explore at the following URL, replacing <my.looker.com> with the address of your Looker instance:

https://<my.looker.com>/explore/i__looker/history

The History Explore can be used as a more detailed version of the Queries panel on the Admin page. It has all of the information on the Queries panel, but goes back further than the latest 50 queries, and has more information about users, roles, groups, etc. Because the History table can grow rather large, Looker automatically truncates its data every 90 days.

What the History Explore is Not

The History Explore includes views titled Look, Dashboard, and User. These views do not list all of the Looks, dashboards and users on your instance. Rather, these views list the Looks, dashboards, and users that have been accessed or active within the last 90 days. To view comprehensive lists of Looks, dashboards, or users, use the Explores described later in this page.

Useful Fields in the History Explore

There are a lot of fields in the History Explore. Here are some of the most commonly-used fields:

  • history.source: The source of a query, such as a Saved Look, Dashboard, Explore, Scheduled Task, API, etc.

  • history.created_time: The date and time an event in the History Explore was created. Each event is linked to a query in Looker. Queries come from the Explore page, dashboards, Looks, API calls, and scheduled events.

  • history.result_source: Whether the results of a query was returned from the cache, or from the database as a fresh query.

  • history.runtime_in_seconds: The amount of time a query took to run.

  • query.model: The LookML model associated with a query.

  • query.explore: The LookML explore associated with a query.

  • query.sql_text: The actual SQL that was run for a query.

Examples

Below are some common uses of the History Explore. Note that you must replace <my.looker.com> at the beginning of the URL with the address of your Looker instance for these links to work.

What is the Average Runtime for Different Models on My Instance?

This Explore lists each model that was queried in the last 90 days, and shows the average runtime for queries based on those models during the previous 90 days:

https://<my.looker.com>/explore/i__looker/history?fields=query.model,history.average_runtime&f[history.result_source]=query&sorts=history.average_runtime+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22history.result_source%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22query%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

What is the Most Popular Dashboard by Query Count?

This Explore lists the titles of every dashboard accessed in the last 90 days, and a count of the number of times each of those dashboards was accessed:

https://<my.looker.com>/explore/i__looker/history?fields=dashboard.title,history.query_run_count&f[dashboard.title]=-NULL&sorts=history.query_run_count+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22dashboard.title%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

Which Explores are Most Popular with Different Roles?

This Explore shows how many times an Explore was run by each role on your instance in the previous 90 days:

https://<my.looker.com>/explore/i__looker/history?fields=history.query_run_count,query.view,role.name&pivots=role.name&f[role.name]=-NULL&sorts=history.query_run_count+desc+0&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22role.name%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

Has Anyone Queried a Specific Explore in the Last 90 Days?

You can use the History Explore to determine if a specific Explore was used in the last 90 days:

  1. Select the fields history.created_date, query.explore, and query.link.
  2. Filter on query.explore, entering the name of the Explore.
  3. Click Run.

Has Anyone Used a Field from a Specific View in the Last 90 Days?

You can use the History Explore to determine if any fields from a specific view were used in the last 90 days:

  1. Select the fields history.created_date, query.explore, and query.link.
  2. Filter on query.fields_used, entering the name of the view followed by a period, such as orders., and selecting contains for the filter condition. The period in your filter will ensure that you only return fields from that view, and not fields from similarly named views.
  3. Click Run.

Look Explore

The Look Explore includes information about all saved Looks on your Looker instance, such as:

  • Who created the Looks and when they were created
  • The fields that make up a Look
  • The Spaces where the Looks are stored

You can access the Look Explore at the following URL, replacing <my.looker.com> with the address of your Looker instance:

https://<my.looker.com>/explore/i__looker/look

Useful Fields in the Look Explore

Some of the most commonly-used fields in the Look Explore are:

  • look.created_date: The date a Look was created.

  • look.deleted_date: The date a Look was deleted. If the Look has not been deleted, this field will be NULL.

  • look.public: Whether or not the Look has been made public.

  • look.public_slug: The hash portion of the Look URL.

  • query.link: A link to the Explore underlying the Look.

  • space.name: The name of the Space where a Look is stored.

Examples

Below are some common uses of the Look Explore. Note that you must replace <my.looker.com> at the beginning of the URL with the address of your Looker instance for these links to work.

How Many Public Looks are There?

This Explore lists all of the Looks on your instance that have been made public:

https://<my.looker.com>/explore/i__looker/look?fields=look.id,look.title,look.link&f[look.public]=Yes&sorts=look.title&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22look.public%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22Yes%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Which Looks Have Been Deleted?

This Explore lists all of the Looks that have been deleted:

https://<my.looker.com>/explore/i__looker/look?fields=look.id,look.title,look.link,look.deleted_date&f[look.deleted_date]=NOT+NULL&sorts=look.title&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22look.deleted_date%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%227%22%2C%22unit%22%3A%22day%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Which Spaces are my Looks Saved In?

This Explore lists all of your Looks, sorted by the Space that they are stored in:

https://<my.looker.com>/explore/i__looker/look?fields=space.name,look.id,look.title,look.link&f[look.deleted_date]=NOT+NULL&sorts=space.name+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22look.deleted_date%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%227%22%2C%22unit%22%3A%22day%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Dashboard Explore

The Dashboard Explore includes information about every dashboard saved on your Looker instance. Similar to the Look Explore, you can find information about your dashboards such as who created them, when they were created, and which Space they reside in. You can also find information about the dashboard settings and elements.

You can access the dashboard Explore at the following URL, replacing <my.looker.com> with the address of your Looker instance:

https://<my.looker.com>/explore/i__looker/dashboard

Useful Fields in the Dashboard Explore

The Dashboard Explore has similar fields to the Look Explore. In addition to those fields, here are some fields that are unique to the Dashboard Explore that are commonly-used:

  • dashboard.refresh_interval: If Auto-Refresh is enabled on the dashboard, how often the dashboard will refresh.

  • dashboard.run_on_load: Whether Run on Load is enabled on the dashboard.

Examples

Below are some common uses of the Dashboard Explore. Note that you must replace <my.looker.com> at the beginning of the URL with the address of your Looker instance for these links to work.

How Many Dashboards Run on Load?

This Explore shows a count of how many dashboards in your instance are configured to Run on Load:

https://<my.looker.com>/explore/i__looker/dashboard?fields=dashboard.count&f[dashboard.run_on_load]=Yes&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22dashboard.run_on_load%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22Yes%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%7D%5D%7D&origin=share-expanded

How Many Frequently Refreshed Dashboards are There?

Similar to Run on Load, the Auto-Refresh setting can affect Looker performance if you have a large number of dashboards that auto-refresh frequently. This Explore shows the number of dashboards that are set to auto-refresh, and how many refresh at each time interval:

https://<my.looker.com>/explore/i__looker/dashboard?fields=dashboard.refresh_interval,dashboard.count&f[dashboard.refresh_interval]=-NULL&sorts=dashboard.count+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22dashboard.refresh_interval%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

User Explore

The User Explore includes information about every user on your Looker instance. It includes information such as:

  • User API credentials

  • User groups

  • Roles, permissions and permission sets assigned to users

  • User details and settings such as name, ID, and whether the user is enabled on the instance

You can access the User Explore at the following URL, replacing <my.looker.com> with the address of your Looker instance:

https://<my.looker.com>/explore/i__looker/user

Useful Fields in the User Explore

Some of the commonly-used fields in the User Explore are:

  • group.name: The name of a group that a user is part of

  • permission_set.permissions: The list of permissions granted to a user

  • role.name: The role assigned to a user

  • user.dev_mode: Whether the user is currently in Development mode

  • user.is_disabled: Whether a user has been disabled on the Looker instance

  • user.edit_link: A link to the page in Looker where you can edit the user information

  • role.edit_link: A link to the page in Looker where you can edit the role information assigned to the user

  • group.edit_link: A link to the page in Looker where you can edit the set of groups a user has

Examples

Below are some common uses of the User Explore. Note that you must replace <my.looker.com> at the beginning of the URL with the address of your Looker instance for these links to work.

Which Users have had Access Disabled?

This Explore lists all users whose access has been disabled:

https://docsexamples.dev.looker.com/explore/i__looker/user?fields=user.name&f[user.is_disabled]=Yes&sorts=user.name&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22user.is_disabled%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22Yes%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%7D%5D%7D&origin=share-expanded

Which Users have a Specific Permission?

This Explore lists all of the users on your instance that have the develop permission:

https://docsexamples.dev.looker.com/explore/i__looker/user?fields=user.name&f[permission_set.permissions]=%25develop%25&sorts=user.name&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22permission_set.permissions%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22develop%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

How Many Users in Each Role on My Instance

This Explore counts the number of active users in each role on the instance. Note that a user can have more than one role assigned. For example, if a user was assigned both the “developer” role and the “admin” role, that user would be counted twice:

https://docsexamples.dev.looker.com/explore/i__looker/user?fields=role.name,user.count&f[user.is_disabled]=No&sorts=user.count+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22user.is_disabled%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22No%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded
Still have questions?
Go to Discourse - or - Email Support
Top