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

Experience writing a Java based DirXML Driver

Based on the customer project, I wrote a DirXML driver which provision users through Novell Identity Manager 3.5.1 to their company intranet portal ( A Plone System). The portal exposed the RESTful API interfaces. So I started looking first at the Novell SOAP driver to see if it fit our needs. But while reading the driver documentation i felt it required too much XSLT knowledge + more customization and testing on the driver. And again it used the Apache HttpClient, Which is more a HttpClient rather then it targets to any specific protocol implementation. So If you could build SOAP messages at your own so it would help you in transporting these message back and forth between IDM and Application. The Novell SOAP driver comes up with two built in configurations "SPML and DSML", but in my case none of them were suitable. I had always wished to write my own DirXML driver at my own, so I thought why not just take this opportunity to fulfill my wish and at the same time get s...
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