Attribute Sync: Find out what gets synced to take specific actions

[FrankSchubert]

When you sync attributes to various targets there could be a need to treat certain attributes differently. E.g. a department change of a user might not (just) require an "update account" but also a "move account", both in Active Directory.

Sadly, the workflow engine doesn't give any good indication on what task is at hand, the workflow variables stay pretty mum about it.

But, fear not. There is help in the right tables and a SQL query can surface that info to make it accessible and actionable within the workflows.

Here is the query:

select distinct sc.display_name as  SYNC_ATTR_NAME,xt1.attr_value as SYNC_ATTR_VALUE

from

avuser.t_av_change_request_details crd

join xmltable (

                '//entry'

                passing xmltype.createxml(crd.additional_data)

                COLUMNS ATTR_NAME varchar(50) path '/entry/string[1]' ,

                        ATTR_VALUE varchar (500) path '/entry/string[2]'

              ) xt1

on 1=1

join avuser.t_extensible_schema_columns sc on sc.name=xt1.attr_name and sc.table_name='T_AV_ACCOUNTS'

where change_requests_id='${access_request_requestID}'

This query will return a single value in both variables (SYNC_ATTR_NAME an SYNC_ATTR_VALUE) IF there is only one attribute that gets synced (and for only one user). If there are more of either one, the variables will contain an array.

Of course you can scope the query down with more WHERE clauses, e.g. filter for a certain (account) attribute name (as the schema table is joined in, you can use the friendly name of the columns, makes this also more robust to move between different stages like DEV, TEST, UAT and PROD). You can  also filter down further to the affected user ID etc. If you do all that, you will end up with single values in each workflow variable.

Enjoy,

Frank

[VenkataRamanaR1]

Frank, this assumes the change request will contain attribute sync type items. So, i guess there will be another condition node before this to first check that this change request is of the relevant type.

[FrankSchubert]

Well, should have been more specific. I assumed that this gets run in a workflow that already only gets called when an attribute sync happens. E.g. via the request workflow attached to the attribute sync pipe or for the sync fulfillment workflow of a specific business source. Or, of course, in whatever workflow this happens and then a decision node in front of it to assure we are in an attribute sync situation.