Tracking User-induced changes to Filters
As of Release 5.6.1, three new tables have been added to the Database logging User engagement with Filters:
- external_filter_log
- external_filter_usage_log
- external_filter_value_log
USE CASE:
- Retrieve all Filters modified by Users on a particular date
SELECT
name as 'filter name',
external_filter_id as 'filter id',
log_action as 'user action',
last_updated_time as 'updated time',
last_updated_by as 'user id'
FROM external_filter_log
WHERE last_updated_time >='2019-07-23'
GROUP BY 1,2,3,4,5
Click to copy
USE CASE:
- Retrieve all Filters that were deleted by Users within a particular time frame
SELECT
name as 'filter name',
external_filter_id as 'filter id',
log_action as 'user action',
last_updated_time as 'updated time',
last_updated_by as 'user id'
FROM external_filter_log
WHERE last_updated_time between '2019-07-23 09-00-00' AND '2019-07-23 11-00-00' AND log_action ='DELETE'
GROUP BY 1,2,3,4,5
Click to copy
USE CASE:
- Retrieve all Filters modified within the last 30 days
- Include the following information:
- Filter names
- Filter creators
- Source of Filter Values
- Objects/Elements using these Filters
- Value settings (if all or specific Values are chosen, or the Filter is set to "ignore")
SELECT
EFL.name as 'Filter Name',
EFL.created_by as 'Creator',
EFL.value_source as 'Source',
EFL.created_time as 'Time created',
EFU.item_id as 'Item using Filter',
EFU.item_type as 'Item Type',
EFU.value_set as 'Value Settings'
FROM external_filter_log as EFL
JOIN external_filter_usage_log as EFU
ON EFL.external_filter_id = EFU.external_filter_id
WHERE DATE(EFL.created_time)>(curdate() - interval 1 month);
Click to copy
