Writing Correlation Queries

IDM processes a correlation query by constructing a query map. The content of the query is generated dynamically, using values from the source object. For each source object, a new query is sent to the target system, using (possibly transformed) values from the source object for its execution.

Queries are run against target resources, either managed or system objects, depending on the mapping. Correlation queries on system objects access the connector, which executes the query on the external resource.

You express a correlation query using a query filter (_queryFilter). For more information about query filters, see "Define and Call Data Queries". The synchronization process executes the correlation query to search through the target system for objects that match the current source object.

To configure a correlation query, define a script whose source returns a query that uses the _queryFilter, for example:

{ "_queryFilter" : "uid eq \"" + source.userName + "\"" }

Using Filtered Queries to Correlate Objects

For filtered queries, the script that is defined or referenced in the correlationQuery property must return an object with the following elements:

The element that is being compared on the target object; for example, uid.
The element on the target object is not necessarily a single attribute. Your query filter can be simple or complex; valid query filters range from a single operator to an entire boolean expression tree.
If the target object is a system object, this attribute must be referred to by its IDM name rather than its ICF nativeName. For example, with the following provisioner configuration, the attribute to use in the correlation query would be uid and not __NAME__:
```
...
    "uid" : {
        "type" : "string",
        "nativeName" : "__NAME__",
        "required" : true,
        "nativeType" : "string"
    }
...  
```
The value to search for in the query.
This value is generally based on one or more values from the source object. However, it does not have to match the value of a single source object property. You can define how your script uses the values from the source object to find a matching record in the target system.
You might use a transformation of a source object property, such as toUpperCase(). You can concatenate that output with other strings or properties. You can also use this value to call an external REST endpoint, and redirect the response to the final "value" portion of the query.

The following correlation query matches source and target objects if the value of the uid attribute on the target is the same as the userName attribute on the source:

"correlationQuery" : {
    "type" : "text/javascript",
    "source" : "var qry = {'_queryFilter': 'uid eq \"' + source.userName + '\"'}; qry"
},

The query can return zero or more objects. The situation assigned to the source object depends on the number of target objects that are returned, and on the presence of any link qualifiers in the query. For information about synchronization situations, see "How Synchronization Situations Are Assessed". For information about link qualifiers, see "Map a Single Source Object to Multiple Target Objects".

Create Correlation Queries Using the Expression Builder

The Expression Builder is a wizard that lets you quickly build expressions using drop-down menu options:

From the navigation bar, click Configure > Mappings.
On the Mappings page, select the mapping to correlate.
From the Mapping Detail page, select the Association tab, and expand Association Rules.
Expand the Association Rules area, click the drop-down menu, and select Correlation Queries.
Click Add Correlation Query.
In the Correlation Query window, click the Link Qualifier drop-down menu, and select a link qualifier.
If you do not need to correlate multiple potential target objects per source object, select the default link qualifier. For more information about linking to multiple target objects, see "Map a Single Source Object to Multiple Target Objects".
Select Expression Builder.
To create an expression, use the drop-down menus to add and remove items, as necessary. List the fields to use for matching existing items in your source to items in your target.
The following example displays an Expression Builder correlation query for a mapping from managed/user to system/ldap/accounts objects. The query creates a match between the source (managed) object and the target (LDAP) object if the value of the givenName or the telephoneNumber of those objects is the same.
After you finish building the expression, click Submit.
On the Mapping Detail page, under the Association Rules area, click Save.

The correlation query created in the previous steps displays as follows in the mapping:

"correlationQuery" : [
    {
        "linkQualifier" : "default",
        "expressionTree" : {
            "any" : [
                "givenName",
                "telephoneNumber"
            ]
        },
        "mapping" : "managedUser_systemLdapAccounts",
        "type" : "text/javascript",
        "file" : "ui/correlateTreeToQueryFilter.js"
    }
]