Skip to main content

NetIQ IDM - JDBC driver - SQL calls from driver (Publisher channel) using XSLT


Recently I was working on a task where we had to call some SQL statements from publisher channel on a JDBC driver to different tables than the ones driver was configured to sync.

The official documentation suggest to achieve this using jdbc-statement but it only schedules them on the subscriber channel, best suited for calling some SQL for stored procedure.

The way I solved it was using XSLT and enabling Subscriber channel.

On the publisher  Command Transformation Channel , I have following XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" version="1.0"> <xsl:param name="srcQueryProcessor" /> <xsl:param name="destQueryProcessor" /> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*" /> </xsl:copy> </xsl:template> <!-- ON ADD | MODIFY --> <xsl:template match="modify[@class-name='Group']|add[@class-name='Group']"> <!-- GET ATTRIBUTE VALUE TO USE WITH SQL --> <xsl:variable name="systmeid" select="*[@attr-name='businessCategory']//value" /> <xsl:variable name="query-doc"> <jdbc:statement type="query"> <jdbc:sql> <xsl:text>SELECT DISTINCT OPERATIONAL_OWNER_ID FROM idm_application_list WHERE SYSTEM_ID =</xsl:text> <xsl:value-of select="$systmeid" /> </jdbc:sql> </jdbc:statement> </xsl:variable> <!-- GET SQL RESULT SET --> <xsl:variable name="result" select="query:query($srcQueryProcessor,$query-doc)" /> <!-- SAVE SQL RESULT INTO TEMP ATTRIBUTE --> <xsl:copy> <xsl:apply-templates select="node()|@*" /> <add-attr attr-name="OperationOwnerId"> <xsl:for-each select="$result//jdbc:value"> <xsl:variable name="row" select="." /> <value type="string"> <xsl:value-of select="$row" /> </value> </xsl:for-each> </add-attr> </xsl:copy> </xsl:template> </xsl:stylesheet>


Actually  I am calling a SQL statement and result of the statement is saved into a temp attribute called  OperationOwnerId,  now this temp attribute is accessible to dirxml policy, remember when you have worked out finished with OperationalOwnerId attribute, just remove it from the current operation.   I have a policy with following rule on Publisher Command Transformation next to XSLT


<rule> <description>Clean up temporary attributes out of the operation for OperationOwnerId</description> <conditions> <and> <if-class-name mode="nocase" op="equal">Group</if-class-name> <if-op-attr name="OperationOwnerId" op="available" /> </and> </conditions> <actions> <do-strip-op-attr name="OperationOwnerId" /> </actions> </rule>
Labels:

Comments

Post a Comment

Popular posts from this blog

NetIQ IDM - SOAP driver -- Handling SOAP service response and manufacturing user association without XSLT

Always pain working with SOAP service and its handling of service response on Query(matching) for user to work with matching policies? Here is the quick wins; without reading  those long lengthy & boring useless SOAP driver blog series at netIQ forums; 1. Make sure you have mapped User class in the schema map with some service attributes 2. Make sure Input and Output policy is registered with all the namespaces you have in your request/response soap messages, example <policy xmlns:soap="http://www.w3.org/2003/05/soap-envelope" 3. I have added operation-data (which is being generated from the Output policy) as part of the SOAP query, but you can skip it if you do not need this. src-dn and association are important to indicate engine for building association.  Operation data will help you to identify operation type and you can carry actions according to it. Inject this on Output ( To support user matching) to send service request: < rule > ...
1 comment
Read more

NetIQ IDM - Adding operation-data to subscriber command transformaiton custom commands

Recently i had to execute EOL cmdlets using psexecute though new NetIQ azure ad driver, since this operation is fire and forget in nature, i would like to track whole request and response for my own generated commands from subscriber command transofrmaiton policy, so i solved it by following policy: < do-set-dest-attr-value direct = "true" name = "psexecute" > < arg-association > < token-resolve datastore = "src" > < arg-dn > < token-text xml:space = "preserve" > {userref} </ token-text > </ arg-dn > </ token-resolve > </ arg-association > < arg-value type = "string" > < token-local-variable name = "cmdlet" /> </ arg-value > </ do-set-dest-attr-value > < do-append-xml-element expression = "../modify[@direct]" na...
2 comments
Read more

Getting into Microsoft Identity Manager ...

Hmm... Microsoft Identity Manager 2010 is out.. but i really wanted to see how the new version is better then its older versions... i have read lots of documentation about FIM2010 and its declarative programming capabilities, MPRs (Management policy rules), workflows, Sets, Group etc, so before touching the fancy parts, i decided to dig into first how the sync-engine or as it previously called MIIS works before doing hands-on with the fancy FIM2010 and the sharepoint based user portal.. Going back to its earlier version and doing hands-on was necessary for me, since FIM2010 documentation always referred the "Classic-rules" as the more powerful then the declarative rules/programming in FIM2010. So i wanted to experience the power into Microsoft IdM before touching the declarative programming(less-power'd) stuff in FIM2010. Having already worked with event-based IdM products such as nOvell identity manager, i was excited to work with the state-based systems such as FIM2010....
Post a Comment
Read more