000030124 - Using SQL Select nodes with RSA Identity Management and Governance

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support on Jan 31, 2018
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000030124
Applies ToRSA Product Set: Identity Governance and Lifecycle
RSA Product/Service Type: Identity Management and Governance
 
IssueA SQL Select node is a workflow component that allows the user to create custom workflow variables. The user enters a SQL query into the node, and then is able to call the results of the query elsewhere in the workflow.
 

Configuration



General Tab



The general tab of the SQL Select node is the same as the general tab of all nodes. There are fields for the node’s name and description, as well as fields to set various status messages for the node. The values input into the general tab do not affect the resulting variable’s name or contents.


Query Tab



The query tab contains two fields, the Query field and the Variable Scope Field

 

Query Field



This is where the select statement is input. If inputting a single statement the query does not need to be terminated by a semicolon. The SQL query has a 4000 character limit, as does the value being retrieved.

 

Variable Scope Field



The Variable Scope Field determines the scope in which the newly created variable can be called. There are three options: Node level, Job Level, and Public level.


  • Node level allows the variable to be called only in the node in which it is defined.
  • Job level allows the variable to be called in the workflow in which it is defined.
  • Public level allows the variable to be called in the workflow in which it is defined, as well as in any of that workflow’s parent/child processes.

The name of the newly created workflow variable is a composite of the Variable Scope and the Column name/alias that is selected in the query. The scope will be lower cased, the column upper cased, as shown here:

 

${[scope]UserData_[COLUMN or ALIAS]}



Example



  1. Let’s say we want to include the submission notes field for a change request in an email; we can use a SQL Select node to capture the value and bring it into the email node.  In the query tab, input the following:

SELECT notes FROM  pv_change_request WHERE name = ${access_request_requestID}

 

This SQL query will return the contents of the notes column which are associated with this change request’s number.


  1. Then set the Variable Scope on the same tab to Job level so it can be used in the rest of the workflow in which it was defined. The resulting variable is ${jobUserData_NOTES}, which can be put in the body of the email node. If, for some, reason we need the variable to have a different name, like if we already have a jobUserData_NOTES variable, use an alias in the SQL query to bring in the variable named whatever we want.

SELECT notes AS 'test' FROM pv_change_request WHERE name = ${access_request_requestID}

 

This would result in a variable named ${jobUserData_TEST}.

Attachments

    Outcomes