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
  • Comment • 0
  • 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.
 

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]


 
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.

Attachments

    Outcomes