MySQL queries 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. 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%';
2. User stats (general)

2.1. Usage activity

2.1.1. Hourly usage activity

USE CASE:

  • Retrieve the 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
2.1.2. Daily usage activity

USE CASE:

  • Retrieve the 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

2.2. Login stats

2.2.1. Last login time per User

USE CASE:

  • Retrieve the last login time for User (user_id)
  • The Metric will show the time when the User entered login and password last time
SELECT user_id, 
IFNULL(last_login_time, 'not available') AS Last_login_date
FROM user
GROUP BY 1
2.2.2. List of Users since last login time

USE CASE:

  • Retrieve the list of Users since last login time
  • This Report will show User information, last login time and the count of days since that time until now
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
3. User Engagement (Objects and Elements)

3.1. Homepage elements

3.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;
3.1.2. Available elements on the Homepage (per User by User types)

USE CASE:

  • Retrieve available elements on the Homepage per User (by User types)
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;
3.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

3.2. Favorites

3.2.1. List of Favorite elements

USE CASE:

  • Retrieve the list of Favorite elements
  • This Report will fetch the list of Favorites for each user
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

3.3. Viewing and usage statistics

3.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;
3.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;
3.3.3. Most viewed elements per month

USE CASE:

  • Retrieve most viewed elements per month (Top 10)
  • This Report will display which elements were viewed the most within the last 30 days
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
3.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
3.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
3.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
3.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
4. User Engagement (Notifications)

4.1. Favorites and Shared Folders

4.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';

4.2. Alerts

4.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';

4.3. Bursts

4.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 contain elements from alert rule scope with visualizations.

USE CASE:

  • Retrieve Burst Recipients with enabled Notifications
SELECT DISTINCT up.user_id, IFNULL(fdei.element_id,nsdi.element_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' AND nsdi.favorite_id>0)
OR (nsd.content_type='tiles' AND nsdi.element_id IS NOT NULL)))
LEFT JOIN favorite_dashboard_element_info AS fdei ON (fdei.favorite_id=nsdi.favorite_id)
LEFT JOIN notification_schedule_distribution_group_recipient AS nsdgr ON (nsdgr.notification_schedule_distribution_id=nsdi.notification_schedule_distribution_id)
LEFT JOIN user_group_member AS ugm ON (ugm.user_group_id=nsdgr.group_id)
LEFT JOIN notification_schedule_distribution_user_recipient AS nsdur ON (nsdur.notification_schedule_distribution_id=nsdi.notification_schedule_distribution_id)
JOIN user_preference AS up ON (up.user_id=IFNULL(ugm.user_id,nsdur.user_id))
WHERE nsd.enabled_ind='Y'
AND up.email_notification_enabled_ind = 'Y'
AND IFNULL(fdei.element_id,nsdi.element_id) IS NOT NULL;
5. User Engagement (Notes, Annotations, Events)
5.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
6. System performance

6.1. System load

6.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;
6.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

6.1.3. Data collection issues

6.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'
6.1.5. Elements with data collection exceeding 60 minutes

USE CASE:

  • Retrieve the list of errors upon data collection
  • This basic query will return all elements with long-running data collection
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
6.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