Search for usage across Fields/Filters/Pivots at same time

[schoolsofthough]

I am trying to use explore/system__activity/history to search across Looker instance for any content (look, dashboard) that references a specific dimension.

Right now I’d have to go to the History Explore and filter the Field Usage, Filters, and Pivots Used dimension in Query view separately.

https://looker.xxxx.com/explore/system__activity/history?fields=query.model,query.view,query.formatted_fields,query.filters,query.formatted_pivots

I was trying to make it easier to filter all 3 at the same time, but using list() function in a table calc…but I’m unable to filter table calcs.

Is there a way to refine/extend a System Activity view/explore to add custom dimensions/measures for my use case? Or is there something in System Activity explore that I’m missing?

[ruthc]

Below is how I do this, but please note that it won’t show show content if it has not been executed in the last 90 days (the History Explore only contains data for the last 90 days). If you want a fool-proof list of everything, including unused content, then you will need to use the Looker API.

https://<insert_your_instance_url>/explore/system__activity/history?fields=content_id,content_title,content_type&filter_expression=%28matches_filter%28%24%7Bquery.formatted_fields%7D%2C+%60%25history.real%5E_dash%5E_id%25%60%29+OR+matches_filter%28%24%7Bquery.filters%7D%2C+%60%25history.real%5E_dash%5E_id%25%60%29+OR+matches_filter%28%24%7Bquery.formatted_pivots%7D%2C+%60%25history.real%5E_dash%5E_id%25%60%29%29+AND+%28matches_filter%28%24%7Bhistory.real_dash_id%7D%2C+%60-NULL%60%29+OR+matches_filter%28%24%7Blook.id%7D%2C+%60not+null%60%29%29&sorts=content_type,content_id&limit=500&column_limit=50&vis=%7B%22show_view_names%22%3Afalse%2C%22show_row_numbers%22%3Atrue%2C%22transpose%22%3Afalse%2C%22truncate_text%22%3Atrue%2C%22hide_totals%22%3Afalse%2C%22hide_row_totals%22%3Afalse%2C%22size_to_fit%22%3Atrue%2C%22table_theme%22%3A%22white%22%2C%22limit_displayed_rows%22%3Afalse%2C%22enable_conditional_formatting%22%3Afalse%2C%22header_text_alignment%22%3A%22left%22%2C%22header_font_size%22%3A%2212%22%2C%22rows_font_size%22%3A%2212%22%2C%22conditional_formatting_include_totals%22%3Afalse%2C%22conditional_formatting_include_nulls%22%3Afalse%2C%22show_sql_query_menu_options%22%3Afalse%2C%22column_order%22%3A%5B%22%24%24%24_row_numbers_%24%24%24%22%2C%22content_type%22%2C%22content_id%22%2C%22content_title%22%5D%2C%22show_totals%22%3Atrue%2C%22show_row_totals%22%3Atrue%2C%22truncate_header%22%3Afalse%2C%22minimum_column_width%22%3A75%2C%22series_column_widths%22%3A%7B%22content_id%22%3A100%2C%22content_type%22%3A90%7D%2C%22type%22%3A%22looker_grid%22%2C%22query_fields%22%3A%7B%22measures%22%3A%5B%5D%2C%22dimensions%22%3A%5B%7B%22align%22%3A%22left%22%2C%22can_filter%22%3Atrue%2C%22category%22%3A%22dimension%22%2C%22default_filter_value%22%3Anull%2C%22description%22%3A%22%22%2C%22enumerations%22%3Anull%2C%22field_group_label%22%3Anull%2C%22fill_style%22%3Anull%2C%22fiscal_month_offset%22%3A0%2C%22has_allowed_values%22%3Afalse%2C%22hidden%22%3Afalse%2C%22is_filter%22%3Afalse%2C%22is_numeric%22%3Afalse%2C%22label%22%3A%22Content+ID%22%2C%22label_from_parameter%22%3Anull%2C%22label_short%22%3A%22Content+ID%22%2C%22map_layer%22%3Anull%2C%22name%22%3A%22content_id%22%2C%22strict_value_format%22%3Afalse%2C%22requires_refresh_on_sort%22%3Afalse%2C%22sortable%22%3Atrue%2C%22suggestions%22%3Anull%2C%22synonyms%22%3A%5B%5D%2C%22tags%22%3A%5B%5D%2C%22type%22%3A%22string%22%2C%22user_attribute_filter_types%22%3A%5B%22string%22%2C%22advanced_filter_string%22%5D%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22view%22%3A%22history%22%2C%22view_label%22%3A%22%22%2C%22dynamic%22%3Atrue%2C%22week_start_day%22%3A%22monday%22%2C%22original_view%22%3A%22history%22%2C%22datatype%22%3Anull%2C%22convert_tz%22%3Atrue%2C%22dimension_group%22%3Anull%2C%22error%22%3Anull%2C%22field_group_variant%22%3Anull%2C%22measure%22%3Afalse%2C%22parameter%22%3Afalse%2C%22primary_key%22%3Afalse%2C%22project_name%22%3Anull%2C%22scope%22%3A%22history%22%2C%22suggest_dimension%22%3A%22content_id%22%2C%22suggest_explore%22%3A%22history%22%2C%22suggestable%22%3Atrue%2C%22lookml_expression%22%3Anull%2C%22is_fiscal%22%3Afalse%2C%22is_timeframe%22%3Afalse%2C%22can_time_filter%22%3Afalse%2C%22time_interval%22%3Anull%2C%22lookml_link%22%3Anull%2C%22period_over_period_params%22%3Anull%2C%22permanent%22%3Anull%2C%22source_file%22%3A%22%22%2C%22source_file_path%22%3A%22%22%2C%22sql%22%3Anull%2C%22sql_case%22%3Anull%2C%22filters%22%3Anull%2C%22times_used%22%3A0%2C%22sorted%22%3A%7B%22desc%22%3Afalse%2C%22sort_index%22%3A0%7D%7D%2C%7B%22align%22%3A%22left%22%2C%22can_filter%22%3Atrue%2C%22category%22%3A%22dimension%22%2C%22default_filter_value%22%3Anull%2C%22description%22%3A%22%22%2C%22enumerations%22%3Anull%2C%22field_group_label%22%3Anull%2C%22fill_style%22%3Anull%2C%22fiscal_month_offset%22%3A0%2C%22has_allowed_values%22%3Afalse%2C%22hidden%22%3Afalse%2C%22is_filter%22%3Afalse%2C%22is_numeric%22%3Afalse%2C%22label%22%3A%22Content+Title%22%2C%22label_from_parameter%22%3Anull%2C%22label_short%22%3A%22Content+Title%22%2C%22map_layer%22%3Anull%2C%22name%22%3A%22content_title%22%2C%22strict_value_format%22%3Afalse%2C%22requires_refresh_on_sort%22%3Afalse%2C%22sortable%22%3Atrue%2C%22suggestions%22%3Anull%2C%22synonyms%22%3A%5B%5D%2C%22tags%22%3A%5B%5D%2C%22type%22%3A%22string%22%2C%22user_attribute_filter_types%22%3A%5B%22string%22%2C%22advanced_filter_string%22%5D%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22view%22%3A%22history%22%2C%22view_label%22%3A%22%22%2C%22dynamic%22%3Atrue%2C%22week_start_day%22%3A%22monday%22%2C%22original_view%22%3A%22history%22%2C%22datatype%22%3Anull%2C%22convert_tz%22%3Atrue%2C%22dimension_group%22%3Anull%2C%22error%22%3Anull%2C%22field_group_variant%22%3Anull%2C%22measure%22%3Afalse%2C%22parameter%22%3Afalse%2C%22primary_key%22%3Afalse%2C%22project_name%22%3Anull%2C%22scope%22%3A%22history%22%2C%22suggest_dimension%22%3A%22content_title%22%2C%22suggest_explore%22%3A%22history%22%2C%22suggestable%22%3Atrue%2C%22lookml_expression%22%3Anull%2C%22is_fiscal%22%3Afalse%2C%22is_timeframe%22%3Afalse%2C%22can_time_filter%22%3Afalse%2C%22time_interval%22%3Anull%2C%22lookml_link%22%3Anull%2C%22period_over_period_params%22%3Anull%2C%22permanent%22%3Anull%2C%22source_file%22%3A%22%22%2C%22source_file_path%22%3A%22%22%2C%22sql%22%3Anull%2C%22sql_case%22%3Anull%2C%22filters%22%3Anull%2C%22times_used%22%3A0%7D%2C%7B%22align%22%3A%22left%22%2C%22can_filter%22%3Atrue%2C%22category%22%3A%22dimension%22%2C%22default_filter_value%22%3Anull%2C%22description%22%3A%22%22%2C%22enumerations%22%3Anull%2C%22field_group_label%22%3Anull%2C%22fill_style%22%3Anull%2C%22fiscal_month_offset%22%3A0%2C%22has_allowed_values%22%3Afalse%2C%22hidden%22%3Afalse%2C%22is_filter%22%3Afalse%2C%22is_numeric%22%3Afalse%2C%22label%22%3A%22Content+Type%22%2C%22label_from_parameter%22%3Anull%2C%22label_short%22%3A%22Content+Type%22%2C%22map_layer%22%3Anull%2C%22name%22%3A%22content_type%22%2C%22strict_value_format%22%3Afalse%2C%22requires_refresh_on_sort%22%3Afalse%2C%22sortable%22%3Atrue%2C%22suggestions%22%3Anull%2C%22synonyms%22%3A%5B%5D%2C%22tags%22%3A%5B%5D%2C%22type%22%3A%22string%22%2C%22user_attribute_filter_types%22%3A%5B%22string%22%2C%22advanced_filter_string%22%5D%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22view%22%3A%22look%22%2C%22view_label%22%3A%22%22%2C%22dynamic%22%3Atrue%2C%22week_start_day%22%3A%22monday%22%2C%22original_view%22%3A%22look%22%2C%22datatype%22%3Anull%2C%22convert_tz%22%3Atrue%2C%22dimension_group%22%3Anull%2C%22error%22%3Anull%2C%22field_group_variant%22%3Anull%2C%22measure%22%3Afalse%2C%22parameter%22%3Afalse%2C%22primary_key%22%3Afalse%2C%22project_name%22%3Anull%2C%22scope%22%3A%22look%22%2C%22suggest_dimension%22%3A%22content_type%22%2C%22suggest_explore%22%3A%22history%22%2C%22suggestable%22%3Atrue%2C%22lookml_expression%22%3Anull%2C%22is_fiscal%22%3Afalse%2C%22is_timeframe%22%3Afalse%2C%22can_time_filter%22%3Afalse%2C%22time_interval%22%3Anull%2C%22lookml_link%22%3Anull%2C%22period_over_period_params%22%3Anull%2C%22permanent%22%3Anull%2C%22source_file%22%3A%22%22%2C%22source_file_path%22%3A%22%22%2C%22sql%22%3Anull%2C%22sql_case%22%3Anull%2C%22filters%22%3Anull%2C%22times_used%22%3A0%7D%5D%2C%22table_calculations%22%3A%5B%5D%2C%22pivots%22%3A%5B%5D%7D%2C%22defaults_version%22%3A1%7D&filter_config=%7B%7D&dynamic_fields=%5B%7B%22category%22%3A%22dimension%22%2C%22expression%22%3A%22if%28is_null%28%24%7Blook.id%7D%29%2C%24%7Bhistory.real_dash_id%7D%2Cconcat%28%24%7Blook.id%7D%2C%5C%22%5C%22%29%29%22%2C%22label%22%3A%22Content+ID%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22dimension%22%3A%22content_id%22%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22string%22%7D%2C%7B%22category%22%3A%22dimension%22%2C%22expression%22%3A%22if%28is_null%28%24%7Bhistory.dashboard_id%7D%29%2C%24%7Blook.title%7D%2C%24%7Bdashboard.title%7D%29%22%2C%22label%22%3A%22Content+Title%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22dimension%22%3A%22content_title%22%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22string%22%7D%2C%7B%22category%22%3A%22dimension%22%2C%22expression%22%3A%22if%28is_null%28%24%7Blook.id%7D%29%2C%5C%22dashboard%5C%22%2C%5C%22look%5C%22%29%22%2C%22label%22%3A%22Content+Type%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22dimension%22%3A%22content_type%22%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22string%22%7D%5D&origin=share-expanded

[schoolsofthough]

this also doesn’t include lookml dashboards right?

[ruthc]

Yes, it includes LookML and user-defined dashboards - history.real_dash_id pulls in both ID’s, and dashboard.id can be used to differentiate between the 2 (LookML dashboards will not have a value for dashboard.id).