Creating SQL Sets

SQL Sets are SQL queries you can build to return a collection of resource objects from the EmpowerID Identity Warehouse, such as all people who have been hired in the last week. These Sets can then be added to Query-Based Collections (SetGroups) and used to make dynamic RBAC delegation assignments.

To initiate creating Sets, users must have the Initiator Access Level for the ProvisionSetWithSetGroup workflow. To create Sets, users need to have an Access Level assignment with the CreateNewSet operation enabled for the Query-Based Collection (SetGroup) resource type. This operation is enabled by default for users with the All Access (EmpowerID Admin) Access Level, which is included with the All Access and or the EmpowerID Configuration Manager Management Roles. If a user has access to run the workflow but not create the Set, EmpowerID routes the request to users with the needed Access Level or role.
You cannot create SQL sets with queries that would delete, drop or update objects in the Identity Warehouse. For example, DELETE FROM PersonCommunicationType WHERE CommunicationTypeID = 2 would not be acceptable. If you try to create a SQL set with such a query, EmpowerID will cancel the operation and display a message stating that the SQL Filter (query) is invalid.
Prerequisites: For EmpowerID to compile sets, the Set Compiler Job must be enabled on at least one EmpowerID Server hosting the Worker Role service.

  • To enable the Set Compiler Job
    1. Log in to the EmpowerID Management Console as an administrative user.
    2. Navigate to the EmpowerID Server and Roles node in Configuration Manager.
    3. From the EmpowerID Server and Roles node, locate the Set Compiler Job in the Configuration Manager and tick the Job checkbox on one of the EmpowerID servers hosting the Worker Role service.

To create a SQL Set

  1. From the Navigation Sidebar, navigate to the Set Groups management page by expanding Identities and clicking Query-Based Collections (SetGroups).
  2. From the Set Groups management page, click the Actions tab and then click Create Query (Set).

  3. In the General section of the Set Details form that appears, do the following:
    1. Select SQL Query from the Query (Set) Type field. This enables you to write a SQL query in the form.
    2. Type a name, display name and description for the Set in the Name, Display Name and Description fields, respectively.
    3. Select the Object Type for which you are creating the Set from the Object Type drop-down. For example, if the Set is returning a list of people, select EmpowerID Person from the drop-down.
  4. In the Filter section of the Set Details form, do the following:
    1. Type a SQL query in the SQL Query field that returns the desired objects. In our example, we want the Set to return all people hired within the last five days.
    2. 
          SELECT PersonGuid, FriendlyName FROM Person 
          WHERE CreatedDate BETWEEN DATEADD(day,-5,GETUTCDATE()) AND GETUTCDATE();
      When writing SQL queries for SQL Sets the Select statement must return the <Object>GUID field and the FriendlyName field, as shown in the above statement. Thus, if you are returning Person objects, you return the PersonGUID field; if you are returning Account objects, you return the AccountGUID field; if you are returning mailboxes, you return the MailboxGUID, etc.
    3. Tick Is Enabled to enable EmpowerID to compile the Set (selected by default).
    4. Click the Schedule Interval drop-down and set the schedule for when and how often the Set should be compiled. In our example, we scheduled the Set to be compiled once every day at 8 am. We did this by:
      • Setting the Start and End dates
      • Setting the Interval to Daily
      • Setting the Iterations to 1
      • Setting the Times (compilation time) to 8:00 am
    5. Tick Do Not Allow Delete to prevent the Set from being deleted in the Web interface.
    6. Tick Create Matching Set Group if you want EmpowerID to create a Set Group (Query Based Collection) for the Set at the time the Set is created.
    7. Click Save.
    8. If you chose to create a matching Set Group, after EmpowerID creates the Set, you can verify the matching Set Group from the Query-Based Collections tab of the Set Group management page.