Using the history explore in the i__looker model


(Ian Ross) #1

Introduction
Admin users have access to an instance’s usage panel which connects directly to the underlying application database. The history explore, which is the topic of this article, can be accessed at:

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

What is history

Every time a query is run in Looker it assigned a unique HISTORY ID, that history ID is the base view of this particular explore. That means this explore is particularly useful for information about user’s queries. Because this table can grow rather large, Looker automatically truncates the data in this table every 90 days, meaning this gives you a 90 day look into query data on the instance.

The history explore can be used as a more detailed admin->query panel, 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, etc.

What the history explore is not

The history explore is not the best place to see ALL users, looks, dashboards on the instance. The reason for this, is because history 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 history. 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 history

There are a lot of fields in the history explore, here are a few particularly useful/interesting fields:

history.source: The source of the query, such as look, dashboard, explore, schedule, API, etc.

history.result_source: The source of the results whether it 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 history

Note you must change the beginning of the URL to match that of your looker instance for these links to work

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

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

What is the most popular dashboard by query count?

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

https://[mycompany].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 used a field from “my_view” in the last 90 days?

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

Has anyone queried “my_explore” in the last 90 days?

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


Ilooker and user engagement
Looker Report Creation
Using Lookerbot for Slack
Context Comments
Issues with embedding a Look
(brettg) #2

Note on one piece of the i__looker history explore if it is being used to investigate database performance - you will want to consider filtering on history.result_source = 'query' to just look at history records that resulted in returns from the connecting db (time recorded is db connection establishment to connection close). Cached result history records currently log runtimes associated with the most recent query result’s runtime.


(Henri Allik) #3

Really useful feature!
How can we make it available for users? There is so much valuable data that content creators could track.


(romain.ducarrouge) #4

Hey @Henri_Allik currently the access to i__looker is for users set as Admin in the instance.

Alternatively, to share this data with non-admin users an admin user could go into i__looker and either download the data to share or directly schedule it to the users that want to see the data.

I will also let our product team know you would want to allow other users to access i__looker!


(Ian) #5

+1 on non admins seeing i_looker.
What would be even better is if i_looker model lookml was made public every release or the project could be extended.
I want to include our extra backed up history and event data which gets wiped from the looker database into analysis and there isn’t a nice/easy way to do this.


(romain.ducarrouge) #6

Hey @IanT, that is a good suggestion! I will let the team know about it and your use case for it.


(Jenni) #7

+1 on backing up history (or not wiping history!). We’d love to be able to show how Looker adoption has grown throughout the year, but can’t because History>Query>Created Date is only for 3 months


(Elliot Park) #8

Thanks for the feedback! I’ll be sure to pass this to the Product Team.


(Adam Weinstein) #9

What does history.result_source = blank indicate? I’ve noticed in recent months about 30% of the result traffic we have doesn’t have a result_source specified.


(philip.martinelli) #10

Hello @Adam_Weinstein,

The result_source is typically null for erroring queries. To dig into what exactly is going on with these queries, try selecting the status and message fields. This will confirm whether the queries are indeed erroring, and if so, why.

Thanks,
Philip M.


(Adam Weinstein) #11

Dug in further, and there’s seems to be a sharp spike where history.status = 'pending'. We recently moved from Postgres to Aurora - I’m wondering if this is an artifact of how Aurora and Looker play together, or if this is potentially a legit issue? I’m not sure how to interpret status='pending', especially for queries that were created weeks ago.


(philip.martinelli) #12

Hey again @Adam_Weinstein,

Pending queries are those which are waiting to be added to the connection pool. They would wait when the connection’s pool or the user’s pool is full. Queries that have been pending for weeks doesn’t sound like expected behavior. It’s possible these queries were killed without a subsequent status update.

This sounds worth looking into, so please reach out to live chat support or open a ticket by emailing support@looker.com.

Thanks,
Philip M.


(Yooni) #13

Hope this is the right place for this question - if not, please point me in the right direction.

I’ve recently taken over administering a small Looker environment for our org. I’m trying to use the history explore to start cleaning up some old models and explores. So far, most things about it have been quite intuitive just like the rest of Looker (props!) but one thing I’m running into is that I can’t seem to filter for query count = 0? Not sure if I’m missing something painfully obvious or if that’s by design of the History explore? I am including User is Looker = No filter but no other special filter. Any insight on this would be much appreciated! In fact, any other tips and tricks on effective ways of cleaning out old and unused models would be also appreciated.


(Peggy Beard) #14

Hi Yooni! One thing to be aware of is that the History explore will only show what has happened. So looking for queries=0 is not really going to show the results you’re looking for.

Have you looked into the option (for Admins) under Browse for “Unused Content” ? It is described in our docs.

If it is more than just looks and dashboards that you are interested in, such as looking for any unused explores, that gets a bit more involved. In that case, we need to cross-reference the list of all explores available against what has been queried or had content built from and then manually delete the ones which haven’t been used recently.


(Yooni) #15

Thanks, Peggy! That makes sense and basically what I wanted to confirm. Not urgent, but I wouldn’t complain if the History explore also included exposed but unused explores/views in subsequent versions of Looker :slight_smile:


(Peggy Beard) #16

That makes complete sense, thanks for the suggestion, Yooni! I’ve called this to the attention of our product team.


(Lewis Osborne) #17

Hey Looker Team (@romain.ducarrouge),

Any update since @IanT & @JenniB comments on whether i__looker data will be available for dates older than 90 days?

I am also very interested in viewing this information particularly to understand the long term development of our Looker users.

Cheers,

Lewis


(sara.leon) #18

Hi Lewis! Thanks for the feedback, I’ve passed it along to our product team. This is a very active request and is on the product team’s radar.


(Lewis Osborne) #19

thanks @sara.leon !