def find_linked_dash(look_ids):
'''
Helper function to find dashboard(s) our linked look is on
'''
######query parameters#######
model = 'system__activity'
view = 'dashboard'
fields = [
'dashboard.id', 'user.id', 'user.first_name', 'user.last_name',
'look.id'
]
filters = {'look.id': ''}
#######update dashboard filter####
look_id_list = look_ids.values
str_of_look_id = ",".join(look_id_list)
filters['look.id'] = str_of_look_id
########construct query#############
updated_query = (models.WriteQuery(model=model,
view=view,
fields=fields,
filters=filters,
limit=-1))
linked_dash_query = (sdk.run_inline_query(
body=updated_query, result_format="csv").splitlines())
linked_dash_df = pd.DataFrame(list(csv.reader(linked_dash_query)))
headers = ['dashboard_id', 'user_id', 'first_name', 'last_name', 'id']
linked_dash_df = linked_dash_df.iloc[1:]
linked_dash_df.columns = headers
return linked_dash_df
def get_unused_content(days):
""" Return unused content given a user-defined days since last accessed """
unused_query = models.WriteQuery(
model="system__activity",
view="content_usage",
fields=[
"dashboard.id", "look.id", "dashboard.created_date",
"look.created_date", "content_usage.content_title",
"content_usage.content_type", "content_usage.embed_total",
"content_usage.api_total", "content_usage.favorites_total",
"content_usage.schedule_total", "content_usage.other_total",
"content_usage.last_accessed_date"
],
pivots=None,
fill_fields=None,
filters={
"content_usage.days_since_last_accessed": f">{days}",
"content_usage.content_type": "dashboard,look"
},
filter_expression=
"NOT(is_null(${dashboard.id}) AND is_null(${look.id}))",
sorts=["content_usage.other_total"],
limit=None)
unused_content = json.loads(
sdk.run_inline_query(body=unused_query, result_format="json"))
return unused_content
def get_unengaged_users(day_threshold: int):
"""Fetch a list of unengaged users based on suppplied day threshold"""
unengaged_users_query = models.WriteQuery(
view="user",
fields=[
"user.id", "user.name", "user.email", "user.created_date",
"user_facts.last_ui_login_date", "history.most_recent_query_date"
],
filters={
"user.created_date": f"before {day_threshold} days ago",
"user_facts.is_looker_employee": "No",
"user_facts.last_api_login_date":
f"before {day_threshold} days ago,NULL",
"user_facts.last_ui_login_date":
f"before {day_threshold} days ago,NULL",
"history.most_recent_query_date":
f"before {day_threshold} days ago,NULL",
"user.is_disabled": "No",
"user_facts.is_admin": "No"
},
sorts=["user_facts.last_ui_login_date"],
model="system__activity")
unengaged_users = json.loads(
sdk.run_inline_query(result_format="json", body=unengaged_users_query))
for i in unengaged_users:
ran_at = strftime("%Y-%m-%d %H:%M:%S", localtime())
i["ran_at"] = ran_at
return unengaged_users
def check_explore_performance(self):
"""Prints a list of the slowest running explores."""
print(
"\bTest 4/6: Checking for the slowest explores in the past 7 days")
request = models.WriteQuery(
model="i__looker",
view="history",
fields=["query.model", "query.view", "history.average_runtime"],
filters={
"history.created_date": "7 days",
"query.model": "-NULL, -system^_^_activity",
},
sorts=["history.average_runtime desc"],
limit=20,
)
resp = self.sdk.run_inline_query("json", request)
slowest_explores = json.loads(resp)
request.fields = ["history.average_runtime"]
resp = json.loads(self.sdk.run_inline_query("json", request))
avg_query_runtime = resp[0]["history.average_runtime"]
if avg_query_runtime:
print(
f"\bFor context, the average query runtime is {avg_query_runtime:.4f}s"
)
self._tabularize_and_print(slowest_explores)
def run_looker_inline(model, view, fields, filters):
try:
mod = models.WriteQuery(model=model,
view=view,
fields=fields,
filters=filters)
sql = sdk.run_inline_query(result_format="sql", body=mod, cache=False)
return sql
except error.SDKError:
print("error")
def get_look_models(look_ids):
"""Collect model info about looks"""
look_query = models.WriteQuery(model="system__activity",
view="look",
fields=["query.model", "look.id"],
filters={"look.id": ','.join(look_ids)},
limit=10000)
look_info = json.loads(
sdk.run_inline_query(body=look_query, result_format="json"))
return look_info
def get_dashboard_models(dashboard_ids):
"""Collect metadata about dashboards"""
dashboard_query = models.WriteQuery(
model="system__activity",
view="dashboard",
fields=["query.model", "dashboard.id"],
filters={"dashboard.id": ','.join(dashboard_ids)},
limit=10000)
dashboard_info = json.loads(
sdk.run_inline_query(body=dashboard_query, result_format="json"))
return dashboard_info
def get_sql(model_name, explore_name, fields):
sdk = looker_sdk.init31()
body = models.WriteQuery(model=model_name,
view=explore_name,
fields=fields,
pivots=[],
filters={},
sorts=[],
limit=50)
sql = sdk.run_inline_query(result_format='sql', body=body)
return sql
def check_db_connections(self):
"""Gets all db connections and runs all supported tests against them.
"""
print("\bTest 1/6: Checking connections")
reserved_names = [
"looker__internal__analytics", "looker", "looker__ilooker"
]
db_connections: Sequence[models.DBConnection] = list(
filter(lambda c: c.name not in reserved_names,
self.sdk.all_connections()))
if not db_connections:
raise exceptions.NotFoundError("No connections found.")
formatted_results = []
for connection in db_connections:
assert connection.dialect
assert isinstance(connection.name, str)
resp = self.sdk.test_connection(
connection.name,
models.DelimSequence(connection.dialect.connection_tests),
)
results = list(filter(lambda r: r.status == "error", resp))
errors = [
f"- {fill(cast(str, e.message), width=100)}" for e in results
]
resp = self.sdk.run_inline_query(
"json",
models.WriteQuery(
model="i__looker",
view="history",
fields=["history.query_run_count"],
filters={"history.connection_name": connection.name},
limit="1",
),
)
query_run_count = json.loads(resp)[0]["history.query_run_count"]
formatted_results.append({
"Connection":
connection.name,
"Status":
"OK" if not errors else "\n".join(errors),
"Query Count":
query_run_count,
})
self._tabularize_and_print(formatted_results)
def get_content_usage():
"""Collect usage stats for all content"""
query = models.WriteQuery(
model="system__activity",
view="content_usage",
fields=["dashboard.id", "look.id", "content_usage.last_accessed_date"],
pivots=None,
fill_fields=None,
filters={"content_usage.content_type": "dashboard,look"},
filter_expression=
"NOT(is_null(${dashboard.id}) AND is_null(${look.id}))",
limit=None)
unused_content = json.loads(
sdk.run_inline_query(body=query, result_format="json"))
return unused_content
def create_query_request(q: models.Query, filters: Dict[str, str]) -> models.WriteQuery:
return models.WriteQuery(
model=q.model,
view=q.view,
fields=q.fields,
pivots=q.pivots,
fill_fields=q.fill_fields,
filters=filters,
sorts=q.sorts,
limit=q.limit,
column_limit=q.column_limit,
total=q.total,
row_total=q.row_total,
subtotals=q.subtotals,
dynamic_fields=q.dynamic_fields,
query_timezone=q.query_timezone,
)
def check_schedule_failures(self):
"""Prints a list of schedules that have failed in the past 7 days."""
print("\bTest 5/6: Checking for failing schedules")
request = models.WriteQuery(
model="i__looker",
view="scheduled_plan",
fields=["scheduled_job.name", "scheduled_job.count"],
filters={
"scheduled_job.created_date": "7 days",
"scheduled_job.status": "failure",
},
sorts=["scheduled_job.count desc"],
limit=500,
)
result = self.sdk.run_inline_query("json", request)
failed_schedules = json.loads(result)
self._tabularize_and_print(failed_schedules)
def create_new_query(query, filter):
return models.WriteQuery(
model=query.model,
view=query.view,
fields=query.fields,
pivots=query.pivots,
fill_fields=query.fill_fields,
filters=filter,
sorts=query.sorts,
limit=query.limit,
column_limit=query.column_limit,
total=query.total,
row_total=query.row_total,
subtotals=query.subtotals,
vis_config=query.vis_config,
dynamic_fields=query.dynamic_fields,
query_timezone=query.query_timezone,
)
def get_last_accessed_content_dates(content_type: str, delinquent_days: int):
query_config = models.WriteQuery(model="system__activity",
view="content_usage",
fields=[
"content_usage.last_accessed_date",
"content_usage.content_id",
"content_usage.content_title",
"content_usage.content_type"
],
filters={
"content_usage.last_accessed_date":
f"before {delinquent_days} days ago",
"content_usage.content_type":
content_type
},
limit=' 5000')
query_response = sdk.run_inline_query(result_format='json',
body=query_config)
query_response = json.loads(query_response)
return query_response
def check_dashboard_errors(self):
"""Prints a list of erroring dashboard queries."""
print(
"\bTest 3/6: Checking for dashboards with erroring queries in the last 7 days" # noqa: B950
)
request = models.WriteQuery(
model="i__looker",
view="history",
fields=["dashboard.title", "history.query_run_count"],
filters={
"dashboard.title": "-NULL",
"history.created_date": "7 days",
"history.dashboard_session": "-NULL",
"history.status": "error",
},
sorts=["history.query_run_ount desc"],
limit=20,
)
resp = self.sdk.run_inline_query("json", request)
erroring_dashboards = json.loads(resp)
self._tabularize_and_print(erroring_dashboards)
def check_dashboard_performance(self):
"""Prints a list of dashboards with slow running queries in the past
7 days"""
print("\bTest 2/6: Checking for dashboards with queries slower than "
"30 seconds in the last 7 days")
request = models.WriteQuery(
model="i__looker",
view="history",
fields=["dashboard.title, query.count"],
filters={
"history.created_date": "7 days",
"history.real_dash_id": "-NULL",
"history.runtime": ">30",
"history.status": "complete",
},
sorts=["query.count desc"],
limit=20,
)
resp = self.sdk.run_inline_query("json", request)
slowest_dashboards = json.loads(resp)
self._tabularize_and_print(slowest_dashboards)
def run_queries(looker_sdk: methods.LookerSDK, test_model):
test_model_name = test_model["name"]
for e in test_model["explores"]:
if e.get("unused", False):
continue
fields_to_query = [
f["name"]
for f in e["fields"]
if not (f.get("unused", False) or f.get("filter_only", False))
]
filters = {
f["name"]: f["value"] for f in e["fields"] if f.get("filter_only", False)
}
looker_sdk.run_inline_query(
result_format="json",
body=models.WriteQuery(
model=test_model_name,
view=e["name"],
fields=fields_to_query,
filters=filters,
),
)
def create_query_request(q, limit: Optional[str] = None) -> ml.WriteQuery:
return ml.WriteQuery(
model=q.get("model"),
view=q.get("view"),
fields=q.get("fields"),
pivots=q.get("pivots"),
fill_fields=q.get("fill_fields"),
filters=q.get("filters"),
filter_expression=q.get("filter_expressions"),
sorts=q.get("sorts"),
limit=q.get("limit") or limit,
column_limit=q.get("column_limit"),
total=q.get("total"),
row_total=q.get("row_total"),
subtotals=q.get("subtotal"),
runtime=q.get("runtime"),
vis_config=q.get("vis_config"),
filter_config=q.get("filter_config"),
visible_ui_sections=q.get("visible_ui_sections"),
dynamic_fields=q.get("dynamic_fields"),
client_id=q.get("client_id"),
query_timezone=q.get("query_timezone"),
)
def get_users(content_type, content_df):
'''
Helper function to find the owner of the content
'''
#construct query parameters with filters
model = 'system__activity'
content_id = content_df['id']
#gets dashboard owner
if content_type == 'dashboard':
view = 'dashboard'
dash_fields = ([
'dashboard.id', 'dashboard.title', 'user.id', 'user.first_name',
'user.last_name', 'user.email'
])
filters = {'dashboard.id': ''}
unused_dash_id_filter_val = ",".join(content_id.values)
filters['dashboard.id'] = unused_dash_id_filter_val
#write query
unused_dash_users = (models.WriteQuery(model=model,
view=view,
fields=dash_fields,
pivots=[],
filters=filters,
limit=-1))
#run query
dashboard_users_df = (pd.DataFrame(
list(
csv.reader(
sdk.run_inline_query(body=unused_dash_users,
result_format='csv').splitlines()))))
dashboard_users_df = dashboard_users_df.iloc[1:]
dashboard_users_df.columns = [
'id', 'content_name', 'user_id', 'first_name', 'last_name', 'email'
]
dashboard_users_df['content_type'] = 'dashboard'
dashboard_users_df = dashboard_users_df.merge(
content_df, on='id').sort_values('last_accessed_date',
ascending=False)
return dashboard_users_df
#gets look owner
elif content_type == 'look':
view = 'look'
look_fields = ([
'look.id', 'look.title', 'user.id', 'user.first_name',
'user.last_name', 'user.email'
])
filters = {'look.id': ''}
unused_look_id_filter_val = ",".join(content_id.values)
filters['look.id'] = unused_look_id_filter_val
#write query
unused_look_users = (models.WriteQuery(model=model,
view=view,
fields=look_fields,
pivots=[],
filters=filters,
limit=-1))
#run query
look_users_df = (pd.DataFrame(
list(
csv.reader(
sdk.run_inline_query(body=unused_look_users,
result_format='csv').splitlines()))))
look_users_df = look_users_df.iloc[1:]
look_users_df.columns = [
'id', 'content_name', 'user_id', 'first_name', 'last_name', 'email'
]
look_users_df['content_type'] = 'look'
look_users_df = (look_users_df.merge(content_df, on='id').sort_values(
'last_accessed_date', ascending=False))
return look_users_df
else:
raise ValueError
def main(sdk_old_instance, sdk_new_instance, look_id):
old_instance_viz = get_viz_config(look_id=look_id)
# Get the Look from DCL instance
old_look = sdk_old_instance.look(look_id=look_id)
old_query = old_look.query
# Get the user who created the ID
old_user_id = old_look.user
old_user_info = sdk_old_instance.user(user_id=old_user_id.id)
## Find user in Local instance based on email:
cred = old_user_info.email
new_user = sdk_new_instance.user_for_credential(credential_type='email',
credential_id=cred)
# User's info in New instance
user_id = new_user.id
folder_id = new_user.personal_space_id
# Create Folder in new instance
to_new_folder = 'Looks moved with API'
try:
folder = models.WriteFolder(name=to_new_folder, parent_id=folder_id)
_ = sdk_new_instance.create_folder(body=folder)
except:
pass
# Find the ID of the created folder
new_folder = sdk_new_instance.search_folders(name=to_new_folder)
new_folder_id = new_folder[0].id
# Build Query's body
create_query = models.WriteQuery(
model=old_query.model,
view=old_query.view,
fields=old_query.fields,
pivots=old_query.pivots,
fill_fields=old_query.fill_fields,
filters=old_query.filters,
filter_expression=old_query.filter_expression,
sorts=old_query.sorts,
limit=old_query.limit,
column_limit=old_query.column_limit,
total=old_query.total,
row_total=old_query.row_total,
subtotals=old_query.subtotals,
vis_config=old_instance_viz,
filter_config=old_query.filter_config,
visible_ui_sections=old_query.visible_ui_sections,
dynamic_fields=old_query.dynamic_fields)
# Get new query's info
new_query = sdk_new_instance.create_query(body=create_query)
# Build Look's body
look_body = models.WriteLookWithQuery(title=old_look.title,
is_run_on_load=True,
query_id=new_query.id,
space_id=new_folder_id,
user_id=user_id,
query=create_query)
# Create Look
_ = sdk_new_instance.create_look(body=look_body)
print(f"Congratulation for moving Look: {look_id} to the new instance")
