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:

  1. external_filter_log
  2. external_filter_usage_log
  3. external_filter_value_log
1. Filter changes per day

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
2. Filters deleted during a particular time

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
3. Filters modified per month (comprehensive analysis)

USE CASE:

  • Retrieve all Filters modified within the last 30 days
  • Include the following information:
    1. Filter names
    2. Filter creators
    3. Source of Filter Values
    4. Objects/Elements using these Filters
    5. 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