Tasks | Using the results of this query, an Archer admin can begin identifying the "expensive" history log fields by considering the following best practices:
- "track all" should be avoided in favor of "track selected"
- history log fields should track the lowest number of fields possible that satisfy business requirements
- a retention policy should be set on every history log field
- history log fields that are on layout should be set to use a link control, not a grid control
Work with business owners of the affected applications to determine the most narrow set of fields that can be tracked, and what would be an appropriate retention policy for the tracked data. Following these discussions, the admin can begin remediation of the expensive fields.
WITH cte AS ( select mt.module_name 'module name', lt.level_name 'level name', ft.field_name 'field name', fdhl.max_track_days 'retention days count', fdhl.max_track_modifications 'retention changes count', case fdhl.track_all_fields when 0 then 'false' when 1 then 'true' end 'track all fields', case fdhl.display_control_id when 15 then 'grid' when 16 then 'link' end 'display control' from tblivfielddefhistorylog fdhl join tblivfielddef fd on fdhl.field_id = fd.field_id join tblfieldtranslation ft on fd.field_id = ft.field_id join tbllevel l on fd.level_id = l.level_id join tblleveltranslation lt on l.level_id = lt.level_id join tblmodule m on m.module_id = l.module_id join tblmoduletranslation mt on m.module_id = mt.module_id ), cte2 AS ( select mt.module_name 'module name', lt.level_name 'level name', ft.field_name 'field name', count(fho.display_field_id) 'count of tracked fields' from tblivfieldhistoryoptions fho join tblfieldtranslation ft on fho.field_id = ft.field_id join tblivfielddef fd on ft.field_id = fd.field_id join tbllevel l on fd.level_id = l.level_id join tblLevelTranslation lt on l.level_id = lt.level_id join tblmodule m on l.module_id = m.module_id join tblmoduletranslation mt on m.module_id = mt.module_id group by mt.module_name, lt.level_name, fd.field_id, ft.field_name ) select cte.[module name], cte.[level name], cte.[field name], cte.[track all fields], cte2.[count of tracked fields], cte.[retention days count], cte.[retention changes count], cte.[display control] from cte2 full outer join cte on cte.[module name] = cte2.[module name] and cte.[level name] = cte2.[level name] and cte.[field name] = cte2.[field name] order by cte.[module name], cte.[field name]
|