SQL Queries for Tracking Changes to Custom Fields

The article contains 3 SQL queries that return the elements list with changes.

Retrieve the Changes History of the Custom Field 

SELECT custom_field_id, field, old_value, new_value, username, updated_on
FROM custom_field_change_log cl
JOIN user u 
WHERE u.user_id=cl. updated_by_user_id

Retrieve the Usage of the Custom Fields for Elements without Values 

SELECT de.element_id, de.name, cf.custom_field_id, cf.name
FROM custom_field_element cfe
INNER JOIN custom_field cf ON cf.custom_field_id = cfe.custom_field_id
INNER JOIN dashboard_element de ON de.element_id = cfe.element_id

Retrieve Values of Custom Fields for Elements 

SELECT it.element_id, it.name, it.custom_field_id, it.cf_name, it.value
FROM (
SELECT de.element_id, de.name, cf.custom_field_id, cf.name AS cf_name, cfe.text_value AS value
FROM custom_field_element cfe
INNER JOIN custom_field cf ON cf.custom_field_id = cfe.custom_field_id
INNER JOIN dashboard_element de ON de.element_id = cfe.element_id
WHERE cf.field_type = 'textarea'
AND LENGTH(cfe.text_value) > 0
UNION ALL
SELECT de.element_id, de.name, cf.custom_field_id, cf.name AS cf_name, u.display_name AS value
FROM custom_field_element cfe
INNER JOIN custom_field cf ON cf.custom_field_id = cfe.custom_field_id
INNER JOIN dashboard_element de ON de.element_id = cfe.element_id
INNER JOIN user u ON u.user_id = cfe.user_id
WHERE cf.value_source = 'users'
UNION ALL
SELECT de.element_id, de.name, cf.custom_field_id, cf.name AS cf_name, cfev.dataset_value AS value
FROM custom_field_element cfe
INNER JOIN custom_field cf ON cf.custom_field_id = cfe.custom_field_id
INNER JOIN dashboard_element de ON de.element_id = cfe.element_id
INNER JOIN custom_field_element_value cfev ON cfev.custom_field_element_id = cfe.custom_field_element_id
WHERE cf.value_source = 'dataset'
AND LENGTH(cfev.dataset_value) > 0
UNION ALL
SELECT de.element_id, de.name, cf.custom_field_id, cf.name AS cf_name, cfv.value AS value
FROM custom_field_element cfe
INNER JOIN custom_field cf ON cf.custom_field_id = cfe.custom_field_id
INNER JOIN dashboard_element de ON de.element_id = cfe.element_id
INNER JOIN custom_field_element_value cfev ON cfev.custom_field_element_id = cfe.custom_field_element_id
INNER JOIN custom_field_value cfv ON cfv.custom_field_value_id = cfev.custom_field_value_id
WHERE cf.value_source = 'manual'
) it