CA Identity Portal (vApp 14.1) - Getting Approver information from CA IM DB.

[Deactivated User]

Hi All,

I'm new to the CA Identity manager. Here I do have a requirement to fetch current "Approver" of all in-progress requests in the IM Portal which I am assuming stored the value in either IM portal DB or IM DB. Could anyone please suggest me the table name where CA IM stores "Pending Approver" information?

This would be a great help since It will save our time to check in IM Portal GUI for every single pending request. Hoping for prompt response!

Thank you! 
Deepak :)

[Lynn McMorrow]

Hi Deepak

You should have a My Requests module that shows you all current approvers automatically.  It will look something like this:

Lynn McMorrow
Solutions Architect

CA, A Broadcom Company

[Deactivated User]

Hi Lynn,

Thanks for prompt reply!!

I need to fetch all in-progress approval requests report with "Approvers" information on which these request are pending. Since there are number of request in progress (~ 500), it is not feasible to check manually. Therefore, looking for a query to fetch list of all these request including approver information.

Thanks!

Deepak :)

[Iyes Dendeni]

If these are provisioning roles assignments that are subject to workflow approval, you can query the database for that. 

Run a sql query on the tasksession12_5 table and retrieve entries where APPROVALSTATUS = In Progress

I'm not sure if this will give you what you want, but there is limited information in the record.

[Lynn McMorrow]

Agreed with Iyes, a report may be what you need. 

If you still want to do this in the portal, you can configure a My Requests scope and do an advanced search.  When I do this, I have a helpdesk request view that allows appropriate users to see all requests in the system from the past year (configurable)

Then the helpdesk user can go to My Requests and change the search to the appropriate one:

The result of that search (again, only for appropriately scoped users) is a list of all in-progress requests.  You can click on each one to see the details of the progress.

If this doesn't work for you, and you'd prefer a report/spreadsheet showing the active requests and current approvers, it's possible that a proper report as Iyes outlined might be the way to go.  Just remember that you may have multi-stage workflow approvals as well as groups of approvers, so you will want to consider the best way/s to parse and organize such a report.  

[Deactivated User]

Hi Lynn,

Thanks for details!

Completely correct, I'm here looking for report/sheet showing the active in progress requests and current approvers. Yes, if I could find current approver for even a single workflow, assuming multi-level workflow apporvers and groups of approvers can be fetch with help of query/code.

Regards,
Deepak :)

[Deactivated User]

HI Lyes,

Thanks for reply!!

I have already checked this table and it is having data related to approved activities. However, it does not contain information related to current approval.
Could you please suggest in which table I could find last activity value as it is having assigning user info:

Regards,
Deepak :)

[Iyes Dendeni]

Hi Deepak,

It should be in that table. Here is a screenshot of my tasksession_125 table which shows a request I submitted which is in the 'In Progress' approval state:

[Deactivated User]

Hi Lyes,

May I know which version it is? Since there is different schema and I am not able to find any approval status or relevant description in tasksession12_5 table of IM DB.
We are on version vApp 14.1 and we have two databases - one for IM DB and another for IM Portal DB. 

Please find below snippet of IM DB table: tesksession12_5

This is from IM Portal DB:
Request Table where I can find the status, not current approver.

I'm trying to find table in both DBs which storing current approver information, from where I'll try to back track the request.

Regards,
Deepak :)

[Iyes Dendeni]

Hi Deepak,

I checked Identity Manager and there is no report OOB to do what you want.
Unfortunately, there is also no report in WP that will give you what you want.

However, all the information you want is in the table WP_ACTI_DATA. I'm afraid you will need to do multiple queries and correlate the results from the different queries based on ACTI_ID.

1. Select ACTI_ID from WP_ACTI_DATA where VAR_NAME=ACTION_PERFORMED and VAR_VALUE=none
2. Use returned ACTI_ID (we call it X here) to select the corresponding approver
    Select VAR_VALUE from WP_ACTI_DATA Where ACTI_ID=X and VAR_NAME=PARTICIPANTS

I'm not sure you can combine the two statements above into a single compound query.

Please let us know of this resolves your issue.

[Deactivated User]

Hi Lyes,

Thanks for the information!

it seems these tables are having the related data. However, I still could not get the current approver information in these tables and relation/mapping of ACTI_ID with request_ID/taskessionID.

Please find below snippets of one ACTI_ID:

Also,  I got this link
https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1717&MID=765439&CommunityKey=783a8a1e-bb2b-473a-a0c3-7be7b1d92c60&tab=digestviewer

Do we have any official CA document or link where DB structure is explained ?

Regards,
Deepak :)

[Iyes Dendeni]

You need to query the WP_ACTI_DATA table and not the WP_ACTI table.

If you run it on the correct table, here is what you would get:

[Deactivated User]

Hi Lyes,

Greetings!

Thanks for information!
My apologies! Yes, WP_ACTI_DATA table is having participant details. Do you know if there is any mapping table which stores this activity ID and tasksessionID information? 

As I'm able to get tesksession ID from request table of IM_portal DB.
Select [BACKENDREQUESTID] from [portaluser].[REQUEST_Backend] where rootID like '%{request_number}%'

And get more information from event12_5 table
SELECT * FROM [imstore].Event12_5 where TASKSESSIONID like '%{BACKENDREQUESTID}%'

Still, there is a gap that to get Activity_ID while knowing request_ID and tasksessionID.

Regards,
Deepak :)

[Praveen Jain]

You can find relation between Tasksession ID/Event ID and workflow activity ID (PROCI_ID) under WP_PROCI_DATA.

Here is an example. This query will return you workflow PROCI_ID. In this example I have used Event ID because my workflow is event based.

select PROCI_ID from WP_PROCI_DATA where VAR_NAME = 'ims-id' and VAR_VALUE = 'dec8061d-0a1e8853-2b2a3eeb-567eb9ad'

[Deactivated User]

Hi Praveen Jain,

Thanks for the information!

Yes, now I'm able to make connection between tables and reached at the approver value in WP_ACTI_DATA from request ID. Do you have any document/link to see all table schema and column information?

Regards,
Deepak :)

[Praveen Jain]

Yes you can find schema description of all the databases in IM tools folder.

Here are the locations in IM tools folder where you can find the necessary documentation.

Workflow DB -

\IAM Suite\Identity Manager\tools\Workpoint\docs\database

Rest of the DBs -

\IAM Suite\Identity Manager\tools\db

[Deactivated User]

Thanks for prompt reply!

I'm using Appliance so not sure how to get this files. Could you please suggest if there is any way to get this information from appliance?

Deepak :)

[Praveen Jain]

Here is tools folder location in vApp -

/opt/CA/IdentityManager/IAM_Suite/IdentityManager/tools

[Deactivated User]

Hi Praveen Jain,

Thanks for information!

I am able to get the pending approval information. Now, along with this, I need the form name with respect to BACKENDREQUESTID. So, I'm not able to make relation between BACKENDREQUESTID  in REQUEST_Backend table  and  NAME in Permission table. 

Could you please help me with identifying form name with respect to particular BACKENDREQUESTID, since multiple BACKENDREQUESTID could be in a single request?

Table details:
---------------
select BACKENDREQUESTID from <PortalUSER>.REQUEST_Backend where rootID='<ROOTID>';
select NAME from <PortalUSER>Permission where ID='<PERMISSIONID>

Thanks & Regards,
Deepak :)

[Deactivated User]

Hi All,

Could anyone please help me out here to get relation between BACKENDREQUESTID  in REQUEST_Backend table  and  NAME in Permission table? Since I'm not able to find the relationship in case of multiple access request in single request.

Thanks in advance!

Deepak