000038546 - Queries to detect workflows and change requests that could overload the Workpoint Server in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Mar 9, 2020Last modified by RSA Customer Support Employee on Mar 27, 2020
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000038546
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.0, 7.1.1, 7.2.0
IssueThere have been issues reported in RSA Identity Governance & Lifecycle regarding aberrant change requests and workflows. See the following related RSA Knowledge Base Articles:
This RSA Knowledge Base Article describes some SQL queries to detect when a change request or workflow is growing in size to the point where it may overload the Workpoint server. These queries are meant as monitoring tools to prevent the issues described in the above articles from occurring. This is particularly true on versions of RSA Identity Governance & Lifecycle prior to 7.1.1 P04.
ResolutionHere are two queries to execute as avuser:
  1. Check the number of oversplit nodes created in the last seven days (modify the number of days to fit your situation):

select split_count, count(*) node_count
  from wp_proci_split_control
where split_count > 2
   and archive_id is null
   and lu_date >= trunc(sysdate - 7.0)
-- Only check Split Nodes created in the last week
group by split_count
order by split_count desc;

  1.   Find the worst offenders by using the SPLIT_COUNT (e.g. 2) from above to find the nodes:

select split_count as job_count, job.name as job_name, node.name as node_name,
job.proci_id, job.proci_db, node.proci_node_id, node.proci_node_db from (
select  proci_id,proci_db, proci_node_id, proci_node_db , split_count from wp_proci_split_control
where split_count > 2 and lu_date >= trunc(sysdate - 7.0)) problemjobs
  join wp_proci job on job.proci_id = problemjobs.proci_id and job.proci_db = problemjobs.proci_db
  join wp_proci_node node on node.proci_node_id = problemjobs.proci_node_id and
node.proci_node_db = problemjobs.proci_node_db;