Question

Auditing User Attribute values with an API script

  • 2 August 2018
  • 6 replies
  • 1098 views

Userlevel 7
Badge

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):
 

 

82a2ddceed79f5e78cade80028d0574b6b4fe6e2.png

 


6 replies

Userlevel 6
Badge

I’m not sure of the total list of logged changes but many are in the system activity “event” and “event attribute” explores. They are not great as only single pieces of information are logged eg. User ID 3 is removed from a group | Group ID 6 was edited - and these 2 events are not linked. Depends on the type of event as to what you can find out about it and there’s a lot of trial and error involved to work out what events are logged and called.

Hi @fabio , 

Does looker retain information on administrative activities such as date-time of addition / removal of groups that the user is a member of?

Userlevel 7
Badge

Hi @Andre_Soares - I don’t know for this whole end-to-end example, but you can see example usage of each endpoint in the API Explorer - including with the language specific SDKs, like Python:

https://developers.looker.com/api/explorer/4.0/methods/User/user_attribute_user_values

@fabio 
 

Does anyone have a model of using the Looker API, using Python?

I need to exactly list all users and which values have a given user attribute.

Userlevel 7
Badge

Hi @DataChico!

I don’t have an updated version on hand, but I can provide some guidance to hopefully assist in adapting it.

This quick solution was actually just starting from that page in order to be run in the same origin as the API endpoint URLs, to get around the browser’s cross-origin restrictions.

There are several alternatives to run this JS code successfully, here are a few:
- Run it from the old API Docs page anyway, despite the deprecation notice
- If you are on newer GCP hosting where the API origin is the same as the UI origin, you may be able to apply a similar method from a page within Looker’s UI
- Looker has added support for CORS since this was posted, so you could also call the endpoints from any origin, with the exception of the initial `login` endpoint which does not support CORS to prevent applications from sending API credentials to the front-end. You would need to add an additional authentication step (e.g. OAuth, or an application server) to retrieve an authorization token.
- Run it from Node.js on the command line

In addition to the above origin-related options, you may have to provide a function to replace `$.ajax()` which is a method provided by the jQuery script that was already on the page. In browser contexts, the built-in `fetch` method should provide very similar functionality

@fabio This is very helpful but do you have an updated version for using the new ‘API Explorer’ functionality? The original API explorer that you link to is now deprecated. Thanks!

Reply