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. 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 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;
Click to copy

5.4. Emails

5.4.1. Tracking Emails (Bursts)

USE CASE:

  • Retrieve count of Bursts sent via email, along with time, count of emails received, count of emails opened, and count of emails clicked through to Metric Insights
SELECT nsd.name 'Burst Name', 
nsrl.run_request_time 'Sent Time', 
count(distinct nsrldeq.user_id) 'Sent', 
count(distinct nsdol.user_id) 'Opens', 
count(distinct nsdcl.user_id) 'Clicks'
FROM notification_schedule_distribution nsd
JOIN notification_schedule_run_log nsrl ON (nsrl.notification_schedule_id=nsd.notification_schedule_id)
LEFT JOIN notification_schedule_run_log_detail_email_queue nsrldeq ON (nsrldeq.ns_run_id=nsrl.ns_run_id)
LEFT JOIN notification_schedule_distribution_opens_log nsdol ON (nsdol.ns_run_id=nsrl.ns_run_id)
LEFT JOIN notification_schedule_distribution_clicked_link nsdcl ON (nsdcl.ns_run_id=nsrl.ns_run_id)
GROUP BY 1,2
ORDER BY nsd.name ASC, nsrl.run_request_time DESC;
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:

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