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

[#1]

Article Number

000038546

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.0, 7.1.1, 7.2.0
 

Issue

There have been issues reported in RSA Identity Governance & Lifecycle regarding aberrant change requests and workflows. See the following related RSA Knowledge Base Articles:

• 000038393 -- Change requests get randomly cancelled and add unrelated activities or continue to provision in RSA Identity Governance & Lifecycle
• 000038545 -- How to detect and rectify a workflow stalled and/or Workpoint server down situation in RSA Identity Governance & Lifecycle

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.
 

Resolution

Here 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;

2.   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;