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