Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Using the History Explore in the i__looker Model

Avatar
Ian Ross
  • Updated
Follow

Introduction

Admin users have direct access to an instance's usage panel, which connects directly to the underlying application database. Also, users with permission that includes see_system_activity have access to the System Activity Explores and dashboards and the internal i__looker database to view usage, history, and other metadata about a Looker instance.

The History Explore can be accessed at:

https://[instance_name].looker.com/explore/i__looker/history

What is the History Explore?

Every time a query is run in Looker, it is assigned a unique history ID, and that history ID is the base view of the History Explore. That means this Explore is particularly useful for information about users' queries. Because this table can grow rather large, Looker automatically truncates the data in this table every 90 days, which gives you a 90-day look into the query data on the instance.

The History Explore can be used as a more detailed Admin Queries page. It has all of the information exposed there, but goes back further than the latest 50 queries, and has more information about users, roles, groups, and so on.

What the History Explore is Not

The History Explore is not the best place to see ALL users, looks, or dashboards on the instance. The History Explore only tracks query events, so if a user has not done anything in 90 days, or a Look or dashboard has not been queried in 90 days, it will not show up in the History Explore.

For all of your content exploration needs, use the following Explores:

  • /explore/i__looker/look
  • /explore/i__looker/dashboard
  • /explore/i__looker/user

Useful Fields in the History Explore

There are a lot of fields in the History Explore. Here are a few particularly useful/interesting ones:

  • history.source: The source of the query, such as a Look, dashboard, explore, schedule, API, and so forth.
  • history.result_source: The source of the results, whether they came back from cache or the database as a query.
  • history.runtime_in_seconds: The amount of time the 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 the query.

What Kinds of Questions Can I Ask of the History Explore?

You must change the beginning of the URL to match that of your Looker instance for the links below to work:

  1. What is the average runtime against different models on my instance?

    https://[instance_name].looker.com/explore/i__looker/history?fields=query.model,history.average_runtime&sorts=history.average_runtime+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%7D&origin=share-expanded

  2. What is the most popular dashboard by query count?

    https://[instance_name].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

  3. Which Explores are most popular with different roles?

    https://[instance_name].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

  4. Has anyone used a field from my_view in the last 90 days?

    https://[instance_name].looker.com/explore/i__looker/history?fields=history.created_date,query.link&f[query.formatted_fields]=%25my%5E_view%25&sorts=history.created_date+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22query.formatted_fields%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22my_view%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

  5. Has anyone queried my_explore in the last 90 days?

    https://[instance_name].looker.com/explore/i__looker/history?fields=history.created_date,query.link&f[query.view]=my%5E_explore&sorts=history.created_date+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22query.view%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22my_explore%22%7D%2C%7B%7D%5D%2C%22id%22%3A5%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded