000036003 - Identifying and resolving issues with RSA Archer History Log fields

• the name of the module that contains the history log field
• the name of the level on which the history log field resides
• the name of the field itself
• whether the field is set to "track all"
• if the field is set to "track selected", the number of fields being tracked
• if a retention policy is set, the number of days or changes to be retained
• the display control is being used to show this field

• "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

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]