Update: If you like this article and want even more, check out Henry, a command-line tool built by an engineer on our team!

When we need to do a big cleanup in our model, we first need to know which fields are not being used in it. The i__looker Explore gets us pretty close, because we can look for fields not used in the last two months. However, since the i__Looker history is discarded after 90 days, there is no way to find fields with no activity in the last 90 days using i__Looker alone.

Nonetheless, the model API can get you this information; we just need to combine the two!

Below is a JS snippet to do it. We just need to get administrative API credentials for this, head to the interactive API Docs on your instance, and paste the script into your JS Console, replacing the client_id and client_secret values with your own, and the modelName with a valid model:

Where to Use This

Enter the URL to the API Documentation URL on your instance:

https://< your domain here >:19999/api-docs/index.html

Then enter your Client ID and Client Secret:

The Code to Use

(async function(){
  var config ={
    modelName:"faa_redshift",
    timeframe: "28 days",
    apiCredentials:{
      //Provide API credentials with the admin permission
      client_id:"9gC5bCTmrSP5KHCcvrnf",
      client_secret:"..."
    }
  }

  console.log("Authenticating")
  var auth = await $.post("/login",config.apiCredentials)
  var api = (verb,method,data)=>$.ajax({
    method:verb,
    url:"/api/3.0/"+method,
    data:verb=="POST"?JSON.stringify(data):data,
    headers:{"Authorization":"token "+auth.access_token}
  })
  var m=config.modelName
  console.log("Getting fields in "+m+"...")
  var model = await api("GET","lookml_models/"+m)
  var exploreNames = model.explores.map(e=>e.name)
  var fields = await exploreNames.reduce(async (chain,e) => {
    var priorFields = await chain
    var explore = await api("GET","lookml_models/"+m+"/explores/"+e)
    return explore.fields.dimensions.concat(explore.fields.measures)
    .map(f=>(m+"::"+e+"/"+f.name))
    .concat(priorFields)
  }
  , await [] )
  console.log("> Fetched "+fields.length+" field definitions")
  console.log("Getting usage for timeframe '"+config.timeframe+"'...")
  var queries = await api("POST","queries/run/json",{
    model:"i__looker",
    view:"history",
    fields:["query.model","query.view","query.formatted_fields","query.formatted_filters","query.sorts","query.formatted_pivots","history.query_run_count"],
    filters:{"history.created_date":config.timeframe,"query.model":m},
    limit:50000
  })
  console.log("> "+queries.length+" queries fetched")
  var fieldUsage = queries.map( q => ([]
  .concat((JSON.parse(q["query.formatted_fields"])||[]).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
  .concat((JSON.parse(q["query.sorts"])||[]).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
  .concat(Object.keys((JSON.parse(q["query.formatted_filters"])||{})).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
  ))
  .reduce(((a,b)=>a.concat(b)),[])
  .reduce((to,field)=>({[field.fid]:(to[field.fid]||0)+field.count, ...to}),{});
  console.table(
    fields
    .map(f=>({field:f,usage:fieldUsage[f]||0}))
    .sort((a,b)=>a.usage-b.usage))
  })()

The result should look something like the following:

Note that this only counts when the fields are used directly. The LookML validator already allows you to detect when a field is referenced from another field.