MySQL statements for various tasks

The queries below are used to retrieve data from the Dashboard database. Therefore, Dashboard DB (SQL) must be selected as a Data Source when creating Datasets/Elements from Dataset/Element Editors in Metric Insights.

1. Upgrade all Regular Users to the Power User role

USE CASE:

  • The provided query will upgrade all Regular Users to Power Users in selected Groups 
UPDATE user
SET is_power_user_ind='Y' 
WHERE user_id IN
(SELECT ugm.user_id
FROM (SELECT * FROM user) AS u
JOIN user_group_member AS ugm ON(u.user_id=ugm.user_id) 
WHERE (u.is_power_user_ind='N' AND u.is_administrator_ind='N') AND ugm.user_group_id IN(<YourTargetedGroupID>));
Click to copy

1.1. Get the group ID for the above query 

SELECT ugm.user_group_id, ugm.user_id, u.username 
FROM user_group_member AS ugm
JOIN user AS u ON(u.user_id=ugm.user_id) 
WHERE (u.is_power_user_ind='N' AND u.is_administrator_ind='N')
ORDER BY ugm.user_group_id;
Click to copy
2. Elements with specific Filter Values

USE CASE:

  • The provided query will retrieve a list of External Reports that are filtered to include ONLY specific values.
SELECT it.element_id, it.name AS filter_name, it.value
FROM (
    SELECT de.element_id, ef.name, efv.value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_value efv ON efv.external_filter_id = ef.external_filter_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'manual'
        AND efu.value_set = 'all'

    UNION ALL

    SELECT de.element_id, ef.name, efv.value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_usage_value efuv ON efuv.external_filter_usage_id = efu.external_filter_usage_id
        INNER JOIN external_filter_value efv ON efv.external_filter_value_id = efuv.external_filter_value_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'manual'
        AND efu.value_set = 'selected'

    UNION ALL

    SELECT de.element_id, ef.name, sv.value_display_name AS value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN segment_value sv ON sv.segment_id = ef.source_segment_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'segment'
        AND efu.value_set = 'all'

    UNION ALL

    SELECT de.element_id, ef.name, sv.value_display_name AS value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_usage_value efuv ON efuv.external_filter_usage_id = efu.external_filter_usage_id
        INNER JOIN segment_value sv ON sv.segment_value_id = efuv.segment_value_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'segment'
        AND efu.value_set = 'selected'
) it
WHERE value like '%19%';
Click to copy
3. User stats (general)

3.1. Usage activity

3.1.1. Hourly usage activity

USE CASE:

  • Retrieve a count of elements viewed per hour 
SELECT DATE_FORMAT(view_time, "%Y-%m-%d %H:00:00") "Date", COUNT(distinct element_id) "Elements Viewed"
FROM dashboard_element_view_log_detail dash
WHERE view_time > :last_measurement_time
GROUP BY 1
Click to copy
3.1.2. Daily usage activity

USE CASE:

  • Retrieve a count of elements viewed per day 
SELECT DATE(view_time) "Date", COUNT(distinct element_id) "Elements Viewed"
FROM dashboard_element_view_log_detail dash
WHERE  view_time > :last_measurement_time
GROUP BY 1
Click to copy

3.2. Login stats

3.2.1. Last login time per User

USE CASE:

  • Retrieve the last login time for User (user_id)
  • This will show the time when a User last entered their login and password
  • Recommendation: use to build a Metric
SELECT user_id, 
IFNULL(last_login_time, 'not available') AS Last_login_date
FROM user
GROUP BY 1
Click to copy
3.2.2. List of Users since last login time

USE CASE:

  • Retrieve a list of Users since last login time 
  • Show User information, last login time, and the count of days since that time until now
  • Recommendation: use to build a Report
SELECT username, first_name, last_name, 
IFNULL(last_login_time, 'Not Available') AS Last_login_date, 
DATEDIFF(CURDATE(),last_login_time) AS Days_since_last_login
FROM user
Click to copy
4. User Engagement (Objects and Elements)

4.1. Homepage elements

4.1.1. Available elements on the Homepage (per User)

USE CASE:

  • Retrieve available elements on the Homepage per User 
select d.user_id as user_id, u.username as username, count(element_id) as number_of_available_elements_on_HP
from user_dashboard_element_instance d
join user u on u.user_id=d.user_id
where in_dashboard_ind_flag = 'Y'
group by 1;
Click to copy
4.1.2. Available elements on the Homepage (per User by User types)

USE CASE:

  • Retrieve available elements on the Homepage per User (by User type) 
select case is_administrator_ind and is_power_user_ind
when is_administrator_ind = 'Y' then 'Administrator'
when is_power_user_ind ='Y' then 'Power User'
else 'Regular User'
end as usertype, d.user_id as user_id,
u.username,
count(element_id) as number_of_available_elements_on_HP
from user_dashboard_element_instance d
join user u on u.user_id=d.user_id
where in_dashboard_ind_flag = 'Y'
group by 2;
Click to copy
4.1.3. All available elements on the Homepage (by count of all Users)

USE CASE:

  • Retrieve the count of all available elements on the Homepage (by count of all Users) 
select (select count(element_id) from user_dashboard_element_instance where in_dashboard_ind_flag = 'Y')/count(user_id)
from user_dashboard_element_instance
Click to copy
4.1.4. Homepage search and the number of returned Tiles (per query)

DESCRIPTION

  • Table homepage_search is used to track searches performed by Users on the Homepage
  • Table homepage_search contains the following: homepage search id, user id, search text, the time of search and the number of Tiles returned

USE CASE:

  • Retrieve data on Homepage searches and the count of Tiles that were returned by search queries 
SELECT * 
FROM homepage_search 
ORDER by homepage_search_id DESC 
limit 100;
Click to copy

4.2. Favorites

4.2.1. List of Favorite elements

USE CASE:

  • Retrieve a list of Favorite elements
  • Fetch a list of Favorites for each user
  • Recommendation: build a Report
SELECT u.user_id AS User_ID, u.username AS User_Name,  fdei.element_id AS Element_ID, de.name AS Element_Name,  f.display_name AS Fovirites_Name
FROM favorite_dashboard_element_info AS fdei
JOIN dashboard_element AS de ON(de.element_id=fdei.element_id)
JOIN user AS u ON(u.user_id=fdei.user_id)
JOIN favorite AS f ON(f.favorite_id=fdei.favorite_id)
GROUP BY fdei. favorite_dashboard_element_id
ORDER BY u.username
Click to copy

4.3. Viewing and usage statistics

4.3.1. Average number of viewed Tiles (per period)

USE CASE:

  • Retrieve the average number of Tiles per period 
SELECT user_id, ifnull(count(DISTINCT element_id)/DATEDIFF(MAX(view_time),MIN(view_time)), 'N/A') as AVG_number_of_tiles_per_period
FROM dashboard_element_view_log_detail
GROUP BY 1;
Click to copy
4.3.2. Average number of viewed Tiles (per month)

USE CASE:

  • Retrieve the average number of Tiles per month 
SELECT user_id, cnt/vt as a
FROM (
SELECT user_id, CONCAT(YEAR(view_time),'-',MONTH(view_time)) AS vt,  count(DISTINCT element_id) AS cnt FROM dashboard_element_view_log_detail
GROUP BY 1
) AS t
GROUP BY 1;
Click to copy
4.3.3. Most viewed elements per month

USE CASE:

  • Retrieve most viewed elements per month (Top 10) 
  • Displays which elements were viewed the most within the last 30 days
  • Recommendation: build a Report
SELECT devld.element_id AS "Element ID", 
de.name AS "Element Name", 
count(distinct devld.user_id) AS "Number of Distinct Portal Users",
count(devld.user_id) AS "Total Number of Views"
FROM dashboard_element_view_log_detail AS devld    
JOIN dashboard_element de ON (devld.element_id = de.element_id)
WHERE DATE(devld.view_time)>(curdate()- interval 1 month)
GROUP BY 1
ORDER BY 4 desc Limit 10
Click to copy
4.3.4. List of last viewed elements

USE CASE:

  • Retrieve the list of last viewed elements
  • This Report will fetch data about the last viewed elements, including User information and viewing time
SELECT u.user_id AS User_id, u.username AS User_name, de.name AS Viewed_element_name, dc.category AS Element_category_name, 
IFNULL(devl.last_view_time, 'Not Available') AS Last_viewed_on  
FROM dashboard_element_view_log AS devl
JOIN user AS u ON(u.user_id=devl.user_id)
JOIN dashboard_element AS de ON(de.element_id=devl.element_id)
JOIN dashboard_category AS dc ON(dc.category_id=de.category_id)
ORDER BY last_view_time DESC
Click to copy
4.3.5. Dimension and time period changes for Metrics (at last View)

USE CASE:

  • Retrieve a Dimension and time period changes 
  • This Report shows what Dimension and time period were selected by User during the last Metric View
SELECT u.username AS User, u.user_id AS User_ID, de.name AS Metric_Name, de.element_id AS Metric_ID, sv.value_display_name AS Seen_Dimension_Values, uco.last_updated_time AS View_time, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(uco.overlay_state,'"interval_unit":"',-1),'}',1),'","interval_value":',' ') AS Last_Viewed_Time_interval
FROM user_chart_overlay AS uco
JOIN user AS u ON(u.user_id=uco.user_id)
JOIN dashboard_element AS de ON(uco.element_id=de.element_id)
JOIN segment_value AS sv ON(uco.segment_value_id=sv.segment_value_id)
ORDER BY u.username, uco.last_updated_time DESC, sv.value_display_name
Click to copy
4.3.6. Default View type for Metrics

USE CASE:

  • Retrieve the default View type for Metrics
  • This Report will show what type of Metric View was selected (Standart, Stoplight, Target, Projection, etc.)
SELECT de.element_id AS Element_id, de.name AS Element_name, devld.view_time AS Viewed_time, u.username AS User
, SUBSTRING(uco.overlay_state,10,POSITION('"' IN REPLACE(uco.overlay_state,'{"view":"',''))-1) AS View_name
 FROM dashboard_element AS de 
 JOIN dashboard_element_view_log_detail AS devld ON(devld.element_id=de.element_id)
 JOIN user_chart_overlay AS uco ON(de.element_id=uco.element_id)
 JOIN user AS u ON(u.user_id=devld.user_id)
 WHERE uco.overlay_state LIKE '{"view":"%'
 GROUP BY u.user_id
Click to copy
4.3.7. Most used Datasets

USE CASE:

  • Retrieve most used Datasets (Top 10)
  • This Report will contain the most used Datasets and the count of elements sourced from them
SELECT de.dataset_id As Dataset_ID, d.name AS Dataset_Name, count(distinct de.element_id) AS Number_of_element
FROM dashboard_element AS de
JOIN dataset AS d ON(de.dataset_id=d.dataset_id)
GROUP BY de.dataset_id
ORDER BY 3 Desc LIMIT 10
Click to copy
5. User Engagement (Notifications)

5.1. Favorites and Shared Folders

5.1.1. All elements in favorite Folders for a Digest with enabled Notifications

DESCRIPTION:

  • Table favorite_dashboard_element_info contains all elements in Favorite Folders including Shared Folders.

USE CASE:

  • Retrieve all elements in favorite Folders for a Digest with enabled Notifications 
SELECT DISTINCT fdei.user_id, fdei.element_id
FROM favorite_dashboard_element_info AS fdei
JOIN favorite AS f ON (f.favorite_id=fdei.favorite_id)
JOIN user_preference AS up ON (up.user_id=fdei.user_id)
WHERE f.include_in_favorites_digest_ind='Y'
AND up.email_notification_enabled_ind = 'Y';
Click to copy

5.2. Alerts

5.2.1. All User Alert Subscriptions for elements with enabled Notifications

DESCRIPTION:

  • Table user_alert_rule is used for storing information about user alert subscriptions since 4.0.
  • Table alert_rule_element_info contain elements from alert rule scope with visualizations.

USE CASE:

  • Retrieve all User Alert Subscriptions for elements with enabled Notifications 
SELECT DISTINCT uar.user_id, arei.visualization_element_id
FROM user_alert_rule AS uar
JOIN alert_rule_element_info AS arei ON (uar.alert_rule_id=arei.alert_rule_id AND uar.element_id=arei.element_id)
JOIN user_preference AS up ON (up.user_id=uar.user_id)
WHERE uar.enabled_ind='Y'
AND up.email_notification_enabled_ind = 'Y';
Click to copy

5.3. Bursts

5.3.1. Burst Recipients with enabled Notifications

DESCRIPTION:

  • Table user_alert_rule is used for storing information about user alert subscriptions since 4.0.
  • Table alert_rule_element_info contains elements from alert rule scope with visualizations.

USE CASE:

  • Retrieve Burst Recipients with enabled Notifications 
SELECT dlarv.notification_schedule_distribution_id as Burst_id, nsd.name AS Burst_name,  nsd.enabled_ind AS Burst_Enabled, u.user_id AS Subscribed_User_id, u.username AS Subscribed_user_name,  u.email AS User_mail
FROM distribution_list_all_recipients_view AS dlarv 
JOIN notification_schedule_distribution AS nsd ON dlarv.notification_schedule_distribution_id=nsd.notification_schedule_distribution_id 
JOIN user AS u ON u.user_id=dlarv.user_id 
WHERE u.enabled_ind='Y'
ORDER BY dlarv.notification_schedule_distribution_id, nsd.name;
Click to copy

The contents of "distribution_list_all_recipients_view" (from the above query) are provided below:

CREATE ALGORITHM=UNDEFINED VIEW `distribution_list_all_recipients_view` AS
    SELECT
        `nsdur`.`user_id` AS `user_id`,
        `nsdur`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
        JOIN `notification_schedule_distribution_user_recipient` as `nsdur` ON `nsdur`.`notification_schedule_distribution_id` = `nsd`.`notification_schedule_distribution_id`
    WHERE `nsd`.`recipient_scope` = 'selected'
    UNION
    SELECT
        `f`.`user_id` AS `user_id`,
        `nsd`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
        JOIN `notification_schedule_distribution_item` as `nsdi` ON `nsdi`.`notification_schedule_distribution_id` = `nsd`.`notification_schedule_distribution_id` AND `nsd`.`content_type` = 'favorites'
        JOIN `favorite` as `f` ON (`f`.`source_shared_favorite_id` = `nsdi`.`favorite_id` OR `f`.`copied_from_favorite_id` = `nsdi`.`favorite_id`)
    WHERE `nsd`.`recipient_scope` = 'all'
    UNION
    SELECT
        `nsd`.`created_by_user_id` AS `user_id`,
        `nsd`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
    WHERE `nsd`.`send_owner_distribution_ind` = 'Y';
Click to copy
6. User Engagement (Notes, Annotations, Events)
6.1. List of User Comments

USE CASE:

  • Retrieve the list of User comments
  • This query will return all comments for Notes, Annotations, and Events
SELECT uc.element_id As ElementID, de.name AS Element_Name, de.type AS Element_Type, sv.value_display_name As Dimension_value, uc.scope AS First_comment_type, un.text AS First_comment, u_un.username AS First_comment_user, un.created_time As First_comment_time, uc.text AS Second_comment, u_uc.username AS Second_comment_user, uc.last_updated_time AS Second_comment_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_note AS un ON(uc.user_note_id=un.user_note_id)
JOIN user AS u_un ON(un.user_id=u_un.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='note' 

UNION

SELECT uc.element_id, de.name, de.type, sv.value_display_name,  uc.scope, ua.annotation_text, u_ua.username, ua.annotation_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_annotation AS ua ON(uc.user_annotation_id=ua.user_annotation_id)
JOIN user AS u_ua ON(ua.user_id=u_ua.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='annotation'

UNION

SELECT uc.element_id, de.name, de.type, sv.value_display_name, uc.scope, ne.name, ne.last_updated_by, ne.last_notable_event_activity_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN notable_event AS ne ON(uc.notable_event_id=ne.notable_event_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='event'

ORDER BY 2
Click to copy
7. System performance

7.1. System load

7.1.1. Count of parallel processes per day for the last month

USE CASE:

  • Retrieve the daily count (maximum and average) of parallel processes for the last month 
SELECT DATE(collection_time), MAX(total_process_count), AVG(total_process_count)
FROM mysql_processlist_log
WHERE collection_time>NOW() - INTERVAL 30 DAY
GROUP BY 1;
Click to copy
7.1.2. System load average per hour

USE CASE:

  • Retrieve the system load average value per hour 
SELECT DATE_FORMAT(collection_time, "%Y-%m-%d %H:00:00"),
AVG(total_process_count)
FROM mysql_processlist_log
WHERE collection_time>NOW() - INTERVAL 30 DAY
GROUP BY 1
Click to copy

7.1.3. Data collection issues

7.1.4. List of errors upon data collection

USE CASE:

  • Retrieve the list of errors upon data collection
  • This basic query will return all elements with errors
SELECT * 
FROM (
    SELECT 'trigger' AS caller,  element_id, segment_value_id, start_time, success_ind, error_message
    FROM update_trigger_event_run_log_detail
    WHERE success_ind = 'N'

    UNION ALL

    SELECT 'editor' AS caller,  element_id, segment_value_id, start_time, success_ind, error_message
    FROM editor_data_collection_detail
    WHERE success_ind = 'N'
) it
WHERE error_message != 'No rows are returned'
Click to copy
7.1.5. Elements with data collection exceeding 60 minutes

USE CASE:

  • This basic query will return all elements with long-running data collection (>= 60 min)
SELECT * 
FROM (
    SELECT 'trigger' AS caller,  element_id, segment_value_id, TIMESTAMPDIFF(MINUTE, start_time, finish_time) AS _mins
    FROM update_trigger_event_run_log_detail
    WHERE success_ind = 'Y'
        AND TIMESTAMPDIFF(MINUTE, start_time, finish_time) >= 60
    UNION ALL
    SELECT 'editor' AS caller,  element_id, segment_value_id, TIMESTAMPDIFF(MINUTE, start_time, finish_time) AS _mins
    FROM editor_data_collection_detail
    WHERE success_ind = 'Y'
        AND TIMESTAMPDIFF(MINUTE, start_time, finish_time) >= 60
) it
ORDER BY _mins DESC
Click to copy
7.1.6. List of overdue Trigger runs

USE CASE:

  • Retrieve the list of Triggers with overdue runs
  • This basic query will return a list of overdue Trigger ids, the start time and reasons for overdue runs
SELECT ute.update_trigger_event_id _id, ute.name, rl.run_id, rl.run_start_time, IF (rl.event_aborted_ind = 'Y', 'Aborted', 'Timed out') _reason
FROM update_trigger_event ute
INNER JOIN update_trigger_event_run_log rl ON rl.update_trigger_event_id = ute.update_trigger_event_id
WHERE rl.run_timed_out_ind = 'Y' OR rl.event_aborted_ind = 'Y'
ORDER BY rl.run_start_time DESC
Click to copy