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

Document created by RSA Customer Support Employee on Feb 28, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000036003
Applies ToRSA Product Set: Archer
RSA Version/Condition: All
IssueThe database tables that store history log data (tblIVContentHistory and tblIVFieldHistory) are frequently among the 10 largest tables in the instance database. In addition to this storage issue, certain history log configurations can also cause significant drains on the performance of the web UI. The first step toward addressing these situations is to identify them. This article includes a SQL query that will pull the following information for all history log fields in an Archer instance:
  • 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 being used to show this field
TasksUsing 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.

cte AS
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'
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
mt.module_name 'module name',
lt.level_name 'level name',
ft.field_name 'field name',
count(fho.display_field_id) 'count of tracked fields'
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
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]
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]

ResolutionThe more history log fields that follow best practices in an Archer instance, the better of the instance will be. Following best practices will slow the growth of the instance DB, and will yield better system performance than having non-compliant history log fields.