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.
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
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.1. 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
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
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
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.1. Homepage elements
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
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
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
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
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
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
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
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
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
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
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
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.1. Favorites and Shared Folders
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
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
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
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
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.1. System load
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
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
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
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
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