Auditing User Attribute values with an API script

[fabio1]

Looker’s API’s are very powerful. We aim to expose as much of the UI functionality as possible in our public API endpoints. So, when something is hard to do with the UI, the API is usually a good bet.

For example, to solve a common need, I wrote an API script for building a report of all your users’ User Attribute values. Simply aquire some administrative API credentials, navigate to your instance’s interactive API docs page (usually https://your-domain.looker.com:19999/api-docs/index.html ) and paste this code into your browser’s dev tools console to give it a try.

Note, there are several options you can adjust in the config option at the top!

(async function(){
var config ={
  testRun:false, //It's slow to fetch attributes for all users, so set this true to just see a sample of 10 users
  outputCsv:false,
  userFilter: u=>!u.is_disabled && !u.verified_looker_employee && u.role_ids.length>0,
  attributeFilter: a=>a.match(/^(timezone|another_value_separated_by_pipes|dot_star_matches_anything|.*)$/),
  respectHidden:true,
  showSources:true,
  apiCredentials:{
    //Provide API credentials with the admin permission
    client_id:"...",
    client_secret:"..."
  }
}

window.progress="N/A"

console.log("Authenticating")
var auth = await $.post("/api/3.0/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}
  })

console.log("Getting attribute definitions...")
var allAttributes = await api("GET","user_attributes",{per_page:5000})
var attributes = allAttributes.filter(att=>config.attributeFilter(att.name))
console.log("> Fetched "+allAttributes.length+" attributes and matched "+attributes.length)
console.log(attributes.map(att=>att.name))

console.log("Getting user list...")
var allUsers = await api("GET","users",{fields:"id,email,is_disabled,group_ids,display_name,first_name,last_name,role_ids,verified_looker_employee",per_page:5000})
var users=allUsers.filter(config.userFilter)
console.log("> Fetched "+allUsers.length+" users and matched "+users.length)

console.log("Fetching user attributes values for each user. Type `progress` to see progress...")
var i=0
for(user of users){
    i++; window.progress = "Requesting "+i+" of "+users.length
	user.attributes = await api("GET","users/"+user.id+"/attribute_values",{fields:"name,value,source"})
    if(config.testRun && i>10){console.log("> Exiting early for test run...");break;}
}
window.progress = "Requested"+i+" of "+users.length
console.log("> Finished fetching user attribute values")

var table = users.map(user => ({
  id:user.id, 
  email:user.email,
  ...attributes.reduce((all,att)=>({
       ...all,
       [att.name +(att.is_system?"":"")+(att.value_is_hidden?"":"")+(att.user_can_view?"":"")+(att.user_can_edit?"":"")]:
         (user.attributes||[]).filter(ua=>ua.name==att.name).map(ua=>((config.respectHidden && att.value_is_hidden)?"[hidden]":ua.value)+(config.showSources?" ("+ua.source+")":"")).join("")
      }),{})
}))



if(config.outputCsv && table[0]){
  var keys=Object.keys(table[0])
  console.log(keys.join(",")+"\n"+
   table.map(row=>keys.map(key=>row[key]+'').map(val=>val.match(/,/)?'"'+val+'"':val).join(",")).join("\n")
    +"\n"
  )
}else{console.table(table)}

})()

And here is what the output looks like (with emails redacted):