Locating Campaign Certification Comments in the Identity Governance Database

Products

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

Issue/Introduction

Identity Governance administrators frequently require access to back-end database tables to generate custom reports regarding campaign approval processes. This article outlines the SQL relationship between tables to retrieve certification comments, including the associated campaign and universe details.

Environment

Identity Governance 14.5

Cause

The Identity Governance schema stores comments and campaign metadata in separate, linked tables. Retrieving full context for a comment requires joining the BusinessFlowComments, BFTaskComments, and BusinessFlows tables.

Resolution

To retrieve certification comments and their associated campaign information, execute a SQL join across the following three tables:

1. BusinessFlowComments: Contains the comment text and the CommentID.
2. BFTaskComments: Links the CommentID to the BusinessFlowId.
3. BusinessFlows: Contains the campaign metadata, including Universe, Owner, and Name, linked by the BusinessFlowId.

Example Query:

SELECT     c.CommentText,     f.Name AS CampaignName,     f.Universe,     f.Owner FROM BusinessFlowComments cJOIN BFTaskComments t ON c.CommentID = t.CommentIDJOIN BusinessFlows f ON t.BusinessFlowId = f.BusinessFlowId;

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region.