Article

(View Disclaimer)

Problem

With the Identity Manager JDBC connector, have you ever wanted obtain data directly from the EventLog pertaining to the current event?

Solution

The following XSLT stylesheet shows how to query the EventLog for PEPETRATOR, EVENT_TYPE, and RECORD_ID. Please note that for collapsed XML documents (containing one or more events), the LEAST value for @event-id is retrieved.

The following 2 GCVs must be defined:

<definition display-name="SQL SELECT statement to retrieve EVENTLOG Information" name="gcvMPTEventlogSQLStatement" type="string">
<value>SELECT PERPETRATOR, TO_CHAR(EVENT_TIME,'Mon DD, YYYY HH12:MI:SS AM') "EVENT-TIME" FROM MPT.EVENTLOG WHERE RECORD_ID=</value>
<description>This SQL is used to retrieve PERPETRATOR and EVENT_TIME from the EVENTLOG.  Do not change this to retrieve more attributes. Only change the DATE format if desired.</description>
</definition>

The following XSLT will do all the work. I have this stylesheet in my Input Transformation.

<stylesheet name="PubIT-SSEventLogPayload">
<xsl:stylesheet exclude-result-prefixes="query cmd dncv" version="1.0" xmlns:cmd="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor" xmlns:dncv="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.DNConverter" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- parameters passed in from the DirXML engine -->
<xsl:param name="srcQueryProcessor"/>
<xsl:param name="destQueryProcessor"/>
<xsl:param name="srcCommandProcessor"/>
<xsl:param name="destCommandProcessor"/>
<xsl:param name="dnConverter"/>
<xsl:param name="fromNds"/>
<xsl:template match="add|modify|delete">
<xsl:message>*** Currently processing the Eventlog Payload XSLT ***</xsl:message>
<xsl:variable name="varEventType" select="name(.)"/>
<xsl:variable name="varDOMEventID" select="@event-id"/>
<xsl:message>Current Document Operation Type: [<xsl:value-of select="$varEventType"/>]</xsl:message>
<xsl:message>Original Document EventID: [<xsl:value-of select="$varDOMEventID"/>]</xsl:message>
<!-- ################################ -->
<!-- Grab either the event-id or the first event-id in a collapsed XML DOM -->
<!-- We need to be careful to account for event-id values which skip over because of non sequence PKs -->
<!-- '1000-1005,1007-1009' and '1000-1010,1015,1017' and '1000,1010-1020,1025' and '1001,1003,1005' for example -->
<!-- Always make sure we grab the correct event-id -->
<!-- ################################ -->
<xsl:variable name="varEventID">
<xsl:choose>
<xsl:when test="contains($varDOMEventID, ',')">
<xsl:message>*** This XML Document is processing multiple EVENTLOG rows, seperated by a [,] ***</xsl:message>
<xsl:variable name="varTempEventType" select="substring-before($varDOMEventID,',')"/>
<!-- Test to see if the parsed EVENT_ID is a range -->
<xsl:if test="contains($varTempEventType, '-')">
<xsl:message>*** Watch Out!! Multiple EVENT_ID values still exist, seperated by a [-] ***</xsl:message>
<xsl:value-of select="substring-before($varTempEventType,'-')"/>
</xsl:if>
<xsl:if test="contains($varTempEventType, '-') != 'true'">
<xsl:message>*** A single EVENT_ID was returned ***</xsl:message>
<xsl:value-of select="substring-before($varDOMEventID,',')"/>
</xsl:if>
</xsl:when>
<xsl:when test="contains($varDOMEventID, '-')">
<xsl:message>*** This XML Document is processing multiple EVENTLOG rows, seperated by a [-] ***</xsl:message>
<xsl:value-of select="substring-before($varDOMEventID,'-')"/>
</xsl:when>
<xsl:otherwise>
<xsl:message>*** This XML Document is processing only one EVENTLOG row ***</xsl:message>
<xsl:value-of select="$varDOMEventID"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:message>Returned Document EventID: [<xsl:value-of select="$varEventID"/>]</xsl:message>
<!-- ################################ -->
<!-- Copy the node and all data through -->
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
<!-- ################################ -->
<xsl:message>attr-name tag Count: [<xsl:value-of select="count(child::*[@attr-name])"/>]</xsl:message>
<!-- ################################ -->
<!-- Determine whether to Query the database based on the number of attr-names in this XML Document -->
<!-- ################################ -->
<xsl:if test="(($varEventType='modify' or $varEventType='add') and count(child::*[@attr-name]) > 0) or ($varEventType='delete')">
<xsl:message>*** Non-zero attr-names in the XDS Document - Query Database and continue processing! ***</xsl:message>
<xsl:variable name="varEVENTLOGInformation">
<xsl:call-template name="searchDatabaseEVENTLOG">
<xsl:with-param name="paramEventID" select="$varEventID"/>
</xsl:call-template>
</xsl:variable>
<xsl:message>Returned EVENTLOG Information: [<xsl:value-of select="$varEVENTLOGInformation"/>]</xsl:message>
<xsl:variable name="varEventLogPerpetrator" select="substring-before($varEVENTLOGInformation, '~')"/>
<xsl:variable name="varEventLogEventTime" select="substring-after($varEVENTLOGInformation, '~')"/>
<xsl:message>About to add operation-data for EVENT-TYPE: <xsl:value-of select="$varEventType"/>
</xsl:message>
<xsl:message>About to add operation-data for PERPETRATOR: <xsl:value-of select="$varEventLogPerpetrator"/>
</xsl:message>
<xsl:message>About to add operation-data for EVENT-TIME: <xsl:value-of select="$varEventLogEventTime"/>
</xsl:message>
<!-- Add operation data -->
<operation-data>
<event-publish-status>
<original>
<xsl:value-of select="$varEventType"/>
</original>
<eventlog-record-id>
<xsl:value-of select="$varDOMEventID"/>
</eventlog-record-id>
<eventlog-perpetrator>
<xsl:value-of select="$varEventLogPerpetrator"/>
</eventlog-perpetrator>
<eventlog-time>
<xsl:value-of select="$varEventLogEventTime"/>
</eventlog-time>
</event-publish-status>
</operation-data>
</xsl:if>
<!-- ################################ -->
<!-- Copy the node through -->
</xsl:copy>
<!-- ################################ -->
</xsl:template>
<xsl:template name="searchDatabaseEVENTLOG">
<xsl:param name="paramEventID"/>
<xsl:message>About to Query the EVENTLOG for EventID: [<xsl:value-of select="$paramEventID"/>]</xsl:message>
<xsl:variable name="qryDatabaseEventLog">
<jdbc:statement jdbc:type="query">
<jdbc:sql>
<xsl:text>~gcvMPTEventlogSQLStatement~</xsl:text>
<xsl:value-of select="$paramEventID"/>
</jdbc:sql>
</jdbc:statement>
</xsl:variable>
<xsl:variable name="qryDatabaseResult" select="query:query($srcQueryProcessor,$qryDatabaseEventLog)"/>
<xsl:variable name="varResultPerpetrator" select="$qryDatabaseResult//jdbc:result-set/jdbc:row/jdbc:column[@jdbc:name='PERPETRATOR']/jdbc:value"/>
<xsl:variable name="varResultTime" select="$qryDatabaseResult//jdbc:result-set/jdbc:row/jdbc:column[@jdbc:name='EVENT-TIME']/jdbc:value"/>
<xsl:value-of select="concat($varResultPerpetrator, '~', $varResultTime)"/>
</xsl:template>
<!-- ################################ -->
<!-- Identity Transformation Template -->
<!-- in the absence of any other templates this will cause -->
<!-- the stylesheet to copy the input through unchanged to the output -->
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<!-- ################################ -->
</xsl:stylesheet>
</stylesheet>

Environment

This has been tested by me with IDM3, and JDBC version 2.1.3, although it will work with any JDBC database.

Disclaimer: As with everything else at Cool Solutions, this content is definitely not supported by Novell (so don't even think of calling Support if you try something and it blows up).

It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.