SQL to return all completed jobs from Workpoint

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

How to return a list of all completed WP jobs for possible deletion.

Environment

Release : 14.3

Component : IdentityMinder(Identity Manager)

Resolution

The below will return all the completed jobs which can be removed.  Please note the first column will tell you the PROC ID which corresponds to the Job ID.

SELECT  *  FROM WP_PROCI, WP_PROCI_DATA,WP_WORK_ITEM,WP_WORK_ITEM_PART WHERE WP_PROCI_DATA.PROCI_ID = WP_PROCI.PROCI_ID AND WP_PROCI_DATA.VAR_NAME = '#####.#######' AND WP_WORK_ITEM.ACTI_DB = WP_WORK_ITEM_PART.ACTI_DB AND WP_WORK_ITEM.WI_ITERATION = WP_WORK_ITEM_PART.WI_ITERATION AND WP_WORK_ITEM.WORK_STATE_ID = 5  AND WP_PROCI_DATA.PROCI_ID = WP_WORK_ITEM.PROCI_ID AND WP_PROCI_DATA.PROCI_DB = WP_WORK_ITEM.PROCI_DB

You can simplify the output further by getting distinct Jobs and the corresponding IDM tasksessions:

SELECT distinct WP_PROCI.PROCI_ID,WP_PROCI.NAME FROM WP_PROCI, WP_PROCI_DATA,WP_WORK_ITEM,WP_WORK_ITEM_PART WHERE WP_PROCI_DATA.PROCI_ID = WP_PROCI.PROCI_ID AND WP_PROCI_DATA.VAR_NAME = '#####.#######' AND WP_WORK_ITEM.ACTI_DB = WP_WORK_ITEM_PART.ACTI_DB AND WP_WORK_ITEM.WI_ITERATION = WP_WORK_ITEM_PART.WI_ITERATION AND WP_WORK_ITEM.WORK_STATE_ID = 5  AND WP_PROCI_DATA.PROCI_ID = WP_WORK_ITEM.PROCI_ID AND WP_PROCI_DATA.PROCI_DB = WP_WORK_ITEM.PROCI_DB order by WP_PROCI.PROCI_ID  asc

A 5 signifies a completed state.  All states listed below:

SELECT distinct WP_PROCI.PROCI_ID,WP_PROCI.NAME FROM WP_PROCI, WP_PROCI_DATA,WP_WORK_ITEM,WP_WORK_ITEM_PART WHERE WP_PROCI_DATA.PROCI_ID = WP_PROCI.PROCI_ID AND WP_PROCI_DATA.VAR_NAME = '#####.#######' AND WP_WORK_ITEM.ACTI_DB = WP_WORK_ITEM_PART.ACTI_DB AND WP_WORK_ITEM.WI_ITERATION = WP_WORK_ITEM_PART.WI_ITERATION AND WP_WORK_ITEM.WORK_STATE_ID = 5  AND WP_PROCI_DATA.PROCI_ID = WP_WORK_ITEM.PROCI_ID AND WP_PROCI_DATA.PROCI_DB = WP_WORK_ITEM.PROCI_DB order by WP_PROCI.PROCI_ID  asc

 

You can replace the 5 with the following values :

 

Work Item state in WP tables - 

WORK_STATE_ID	State Name
1	Planned
2	Pending
3	Available
4	Open
5	Complete
6	Cancelled
7	Open and Complete
8	Suspended
9	Open and suspended