A Sample Code for a Custom Post-Processor
The sample post-processor in this section gets the group entitlements from the Active Directory integration. The Active Directory integration uses a child table to store the group membership.
Create a table to store the information you need in the reporting database by using the following SQL scripts.
CREATE SEQUENCE UPA_UD_ADUSRC_SEQ
INCREMENT BY 1
START WITH 1
CACHE 20
/*==============================================================*/
/* Table: UPA_UD_ADUSRC */
/*==============================================================*/
CREATE TABLE UPA_UD_ADUSRC (
UPA_UD_ADUSRC_KEY NUMBER(19) NOT NULL,
UPA_RESOURCE_KEY NUMBER(19) NOT NULL,
OIU_KEY NUMBER(19) NOT NULL,
UD_ADUSRC_GROUPNAME VARCHAR2(256) NOT NULL,
STATUS VARCHAR2(7),
UPA_UD_ADUSRC_EFF_FROM_DATE TIMESTAMP NOT NULL,
UPA_UD_ADUSRC_EFF_TO_DATE TIMESTAMP,
CREATE_DATE TIMESTAMP NOT NULL,
UPDATE_DATE TIMESTAMP NOT NULL,
CONSTRAINT PK_UPA_UD_ADUSRC PRIMARY KEY (UPA_UD_ADUSRC_KEY)
)
COMMENT ON TABLE UPA_UD_ADUSRC IS
'Stores AD group entitlements'
CREATE INDEX IDX_UPA_UD_ADUSRC_EFF_FROM_DT ON UPA_UD_ADUSRC (
UPA_UD_ADUSRC_EFF_FROM_DATE ASC
)
Use the following code for the custom post-processor:
package sample.audit.processor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.sql.Types;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import com.thortech.xl.audit.auditdataprocessors.CustomAuditDataProcessor;
import com.thortech.xl.audit.engine.AuditData;
import com.thortech.xl.audit.exceptions.AuditDataProcessingFailedException;
import com.thortech.xl.util.logging.LoggerMessages;
public class ADUserGroupMembershipProcessor extends CustomAuditDataProcessor {
private static final String CHANGE_TAG = "Change";
private static final String ATTRIBUTE_TAG = "Attribute";
private static final String NAME_ATTRIBUTE = "name";
private static final String CHANGE_LOCATION_ATTRIBUTE = "where";
private static final String ACTION_ATTRIBUTE = "action";
private static final String CHILD_DATA_PREFIX = "/Data";
private static final String RESOURCE_DATA_PREFIX = "/ProcessData/Children/Child";
private static final String RESOURCE_PROFILE_PREFIX = "/UserProfileSnapshot/ResourceProfile/ResourceInstance";
private static final String AD_RESOURCE_NAME = "AD User";
/*private static final String[] UPA_UD_ADUSRC_COLUMNS =
{"UPA_UD_ADUSRC_KEY", "OIU_KEY", "UD_ADUSRC_KEY", "UD_ADUSRC_GROUPNAME",
"UPA_UD_ADUSRC_EFF_FROM_DATE", "UPA_UD_ADUSRC_EFF_TO_DATE",
"CREATE_DATE", "UPDATE_DATE"};*/
public void processAuditData(Connection operationalDB,
Connection reportingDB, List auditDataList, Timestamp auditEpoch)
throws AuditDataProcessingFailedException {
for (Iterator iter = auditDataList.iterator(); iter.hasNext();) {
AuditData auditData = (AuditData) iter.next();
// Retrieve data from AuditData value object
//String auditeeID = auditData.getAuditeeID();
List changeElements = getChangeElements(auditData.getChanges());
// Retrieve AD User Group Membership related changes
List ADUserGrpMembershipChangeElements =
getADUserGroupMembershipChangeElements(changeElements);
// Process change elements
for (Iterator iterator = ADUserGrpMembershipChangeElements.iterator();iterator.hasNext();) {
Element changeElement = (Element) iterator.next();
// Retrieve the resource instance key (OIU_KEY) from the XPath expression
long resourceInstanceKey = getResourceInstanceKey(changeElement);
// Get the object name for this resource instance key
String resName = getResourceName(auditData.getUpdatedSnapshot(), resourceInstanceKey);
if(resName == null || !resName.equals(AD_RESOURCE_NAME))
continue; // this is not the AD User resource so, skip it and check the next one...
// Retrieve the child table key (UD_ADUSRC_KEY)
long UDADUSRCKey = getUDADUSRCKey(changeElement);
// Retrieve the current record, if present
HashMap ADUserGroupMembershipProfile =
readADUserGroupMembershipData(reportingDB, resourceInstanceKey, UDADUSRCKey);
// Reset the default columns
ADUserGroupMembershipProfile.put("UPA_UD_ADUSRC_KEY",null);
ADUserGroupMembershipProfile.put("OIU_KEY",null);
ADUserGroupMembershipProfile.put("UD_ADUSRC_KEY",null);
// Apply the changes
String action = changeElement.getAttribute(ACTION_ATTRIBUTE);
if (action.equalsIgnoreCase("Delete")) {
ADUserGroupMembershipProfile.put("STATUS","DELETE");
} else {
ADUserGroupMembershipProfile.put("STATUS",action.toUpperCase());
Element groupNameElement =
getFirstChildElementByName(changeElement,ATTRIBUTE_TAG,NAME_ATTRIBUTE,"UD_ADUSRC_GROUPNAME");
Attribute attrDetails = getAttributeDetails(groupNameElement);
ADUserGroupMembershipProfile.put("UD_ADUSRC_GROUPNAME",attrDetails.getNewValue());
}
// Set values for the default columns
ADUserGroupMembershipProfile.put("OIU_KEY",new Long(resourceInstanceKey));
ADUserGroupMembershipProfile.put("UD_ADUSRC_KEY",new Long(UDADUSRCKey));
ADUserGroupMembershipProfile.put("UPA_UD_ADUSRC_EFF_FROM_DATE", auditEpoch);
ADUserGroupMembershipProfile.put("UPA_UD_ADUSRC_EFF_TO_DATE", null);
ADUserGroupMembershipProfile.put("CREATE_DATE", new Timestamp(System.currentTimeMillis()));
ADUserGroupMembershipProfile.put("UPDATE_DATE", new Timestamp(System.currentTimeMillis()));
// Update existing active record if present
updateActiveADUserGroupMembershipProfile(reportingDB, resourceInstanceKey, UDADUSRCKey, auditEpoch);
// Insert new record
insertNewADUserGroupMembershipProfile(reportingDB, ADUserGroupMembershipProfile);
}
}
}
private long insertNewADUserGroupMembershipProfile(Connection reportingDB,
HashMap ADUserGroupMembershipProfile)
throws AuditDataProcessingFailedException {
long key = 0;
try {
String insertSQL =
generateNewADUserGroupMembershipInsertSQL(reportingDB,ADUserGroupMembershipProfile);
key = executeInsert(reportingDB, insertSQL, ADUserGroupMembershipProfile);
} catch (SQLException e) {
String errMsg = "Unable to insert new AD User Group Membership Profile";
throw new AuditDataProcessingFailedException(errMsg,e);
}
return key;
}
private String generateNewADUserGroupMembershipInsertSQL(Connection reportingDB,
HashMap ADUserGroupMembershipProfile) throws SQLException {
String valuesPlaceHolder = "";
String columnNames = "";
String dbType = reportingDB.getMetaData().getDatabaseProductName();
if (dbType.startsWith("Oracle")) {
valuesPlaceHolder = "?, ";
columnNames = "UPA_UD_ADUSRC_KEY, ";
}
for (Iterator iter = ADUserGroupMembershipProfile.keySet().iterator(); iter.hasNext();) {
String columnName = (String) iter.next();
Object columnValue = ADUserGroupMembershipProfile.get(columnName);
if (!columnName.equals("UPA_UD_ADUSRC_KEY") && columnValue != null) {
valuesPlaceHolder += "?, ";
columnNames += columnName + ", ";
}
}
// Trim the place holder variable and column names variable
valuesPlaceHolder = (valuesPlaceHolder.trim()).substring(0,valuesPlaceHolder.length()-2);
columnNames = (columnNames.trim()).substring(0,columnNames.length()-2);
String insertSQL = "INSERT INTO UPA_UD_ADUSRC (" + columnNames + ") " +
"VALUES (" + valuesPlaceHolder + ")";
return insertSQL;
}
private void updateActiveADUserGroupMembershipProfile(Connection reportingDB,
long resourceInstanceKey, long UDADUSRCKey, Timestamp auditEpoch)
throws AuditDataProcessingFailedException {
String updateSQL = "UPDATE UPA_UD_ADUSRC " +
"SET UPA_UD_ADUSRC_EFF_TO_DATE=?" +
"WHERE OIU_KEY=?" +
"AND UD_ADUSRC_KEY=?" +
"AND UPA_UD_ADUSRC_EFF_TO_DATE is null";
try {
PreparedStatement pstmt = reportingDB.prepareStatement(updateSQL);
pstmt.setTimestamp(1,auditEpoch);
pstmt.setLong(2,resourceInstanceKey);
pstmt.setLong(3,UDADUSRCKey);
pstmt.executeUpdate();
} catch (SQLException e) {
String errMsg = "Failed to update active AD user group membership profile. " +
"Data in UPA_UD_ADUSRC could be inconsistent";
if (dbLogger.isDebugEnabled())
dbLogger.debug(errMsg,e);
throw new AuditDataProcessingFailedException(errMsg, e);
}
}
/**
*
* @param operationalDB
* @param resourceInstanceKey
* @param UDADUSRCKey
* @return
* @throws AuditDataProcessingFailedException
*/
private HashMap readADUserGroupMembershipData(Connection reportingDB,
long resourceInstanceKey, long UDADUSRCKey)
throws AuditDataProcessingFailedException {
String query = "SELECT * " +
"FROM UPA_UD_ADUSRC " +
"WHERE OIU_KEY=" + resourceInstanceKey + " " +
"AND UD_ADUSRC_KEY=" + UDADUSRCKey + " " +
"AND UPA_UD_ADUSRC_EFF_TO_DATE is null";
try {
return transformADUserGroupMembershipProfile(executeQuery(reportingDB, query));
} catch (SQLException e) {
throw new AuditDataProcessingFailedException("Unable to read data from " +
"JDBC resultset", e);
} catch (Exception e) {
String errMsg = "AD User Group Membership information stored in " +
"UPA_UD_ADUSRC is inconsistent";
if (dbLogger.isDebugEnabled())
dbLogger.debug(errMsg, e);
throw new AuditDataProcessingFailedException(errMsg, e);
}
}
/**
*
* @param result
* @return
* @throws Exception
*/
private HashMap transformADUserGroupMembershipProfile(ResultSet result)
throws Exception {
HashMap ADUserGroupMembershipProfile = new HashMap();
ResultSetMetaData metadata = result.getMetaData();
// Move the cursor to the beginning of the resultset
if (result.next()) {
//
for (int i = 0; i < metadata.getColumnCount(); i++) {
Object columnValue = null;
String columnName = metadata.getColumnName(i+1);
int columnType = metadata.getColumnType(i+1);
switch (columnType) {
case Types.INTEGER:
columnValue = new Long(result.getLong(i+1));
break;
case Types.VARCHAR:
case Types.CHAR:
case Types.LONGVARCHAR:
columnValue = result.getString(i+1);
break;
case Types.TIMESTAMP:
columnValue = result.getTimestamp(i+1);
break;
default:
columnValue = null;
}
if (result.wasNull()) {
columnValue = null;
}
ADUserGroupMembershipProfile.put(columnName, columnValue);
}
// Check if more than one record was returned. If so throw an exception
if (result.next()) {
String errMsg = "More than one active record found for AD group" +
result.getString("UD_ADUSRC_GROUPNAME");
throw new Exception(errMsg);
}
}
return ADUserGroupMembershipProfile;
}
/**
*
* @param changeElement
* @return
*/
private long getResourceInstanceKey(Element changeElement) {
long resourceInstanceKey = 0;
String changeLocation = changeElement.getAttribute(CHANGE_LOCATION_ATTRIBUTE);
int keyStartPosition = changeLocation.indexOf(RESOURCE_PROFILE_PREFIX)+RESOURCE_PROFILE_PREFIX.length()+7;
int keyEndPosition = keyStartPosition+changeLocation.substring(keyStartPosition).indexOf("'")-1;
resourceInstanceKey = Long.parseLong(changeLocation.substring(keyStartPosition,keyEndPosition+1));
return resourceInstanceKey;
}
/**
*
* @param changeElement
* @return
*/
private String getResourceName(Document snapshot, long resourceInstanceKey) {
Element parentElement = snapshot.getDocumentElement();
NodeList childNodes = parentElement.getChildNodes();
for (int i = 0; i < childNodes.getLength(); i++) {
Node childNode = childNodes.item(i);
if ((childNode.getNodeType() == Node.ELEMENT_NODE) &&
childNode.getNodeName().equals("ResourceProfile")) {
NodeList resourceProfileNodeList = childNode.getChildNodes();
for (int j = 0; j < resourceProfileNodeList.getLength(); j++) {
Node resNode = childNodes.item(j);
if ((resNode.getNodeType() == Node.ELEMENT_NODE) &&
resNode.getNodeName().equals("ResourceInstance")) {
Element resourceInstanceElement = (Element)resNode;
String key = resourceInstanceElement.getAttribute("key");
if(key != null && Long.parseLong(key) == resourceInstanceKey)
{
Element name = getFirstChildElementByName(resourceInstanceElement, ATTRIBUTE_TAG, NAME_ATTRIBUTE, "Objects.Name");
return name.getFirstChild().getNodeValue();
}
}
}
}
}
return null;
}
/**
*
* @param changeElement
* @return
*/
private long getUDADUSRCKey(Element changeElement) {
long UDADUSRCKey = 0;
String changeXPath = changeElement.getAttribute(CHANGE_LOCATION_ATTRIBUTE);
String processDataXPath = changeXPath.substring(changeXPath.indexOf(RESOURCE_DATA_PREFIX));
String childDataXPath = processDataXPath.substring(processDataXPath.indexOf(CHILD_DATA_PREFIX));
int keyStartPosition = CHILD_DATA_PREFIX.length()+7;
int keyEndPosition = keyStartPosition+childDataXPath.substring(keyStartPosition).indexOf("'")-1;
UDADUSRCKey = Long.parseLong(childDataXPath.substring(keyStartPosition, keyEndPosition+1));
return UDADUSRCKey;
}
/**
*
* @param changeElements
* @return
*/
private List getADUserGroupMembershipChangeElements(List changeElements) {
List ADUserGrpMembershipChangeElements = new ArrayList();
for (Iterator iter = changeElements.iterator(); iter.hasNext();) {
Element change = (Element) iter.next();
if (isChildrenData(change.getAttribute(CHANGE_LOCATION_ATTRIBUTE)))
ADUserGrpMembershipChangeElements.add(change);
}
return ADUserGrpMembershipChangeElements;
}
/**
*
* @param attribute
* @return
*/
private boolean isChildrenData(String changeLocation) {
if (changeLocation.startsWith(RESOURCE_PROFILE_PREFIX) &&
changeLocation.indexOf(RESOURCE_DATA_PREFIX, RESOURCE_PROFILE_PREFIX.length()) > 0)
return true;
else
return false;
}
/**
*
* @param connection
* @param query
* @return
*/
protected ResultSet executeQuery(Connection connection, String query) {
ResultSet result = null;
try {
Statement stmt = connection.createStatement();
if (stmt.execute(query)) {
result = stmt.getResultSet();
}
} catch (SQLException e) {
if (dbLogger.isDebugEnabled()) {
dbLogger.debug(LoggerMessages.getMessage("DBQueryExecutionError", query),e);
}
}
return result;
}
/**
*
* @param connection
* @param userGroupMembershipProfile
* @param updateSQL
*/
protected long executeInsert(Connection connection, String insertSQL,
HashMap ADUserGroupMembershipProfile) throws SQLException {
PreparedStatement insertStmt = connection.prepareStatement(insertSQL);
String dbType = connection.getMetaData().getDatabaseProductName();
long newKey = 0;
int columnIndex = 1;
//
// Get new key for Oracle and set it into the prepared stmt
if (dbType.startsWith("Oracle")) {
ResultSet nextValRS = executeQuery(connection, "select UPA_UD_ADUSRC_SEQ.nextval from dual");
long nextVal = nextValRS.getLong(1);
insertStmt.setLong(columnIndex++,nextVal);
newKey = nextVal;
}
// Set column names and values for other columns in UPA_UD_ADUSRC
for (Iterator iter = ADUserGroupMembershipProfile.keySet().iterator(); iter.hasNext();) {
String columnName = (String) iter.next();
Object columnValue = ADUserGroupMembershipProfile.get(columnName);
if (!columnName.equals("UPA_UD_ADUSRC_KEY") && columnValue != null) {
if (columnValue.getClass().getName().endsWith("Long")) {
insertStmt.setLong(columnIndex++,((Long)columnValue).longValue());
} else if (columnValue.getClass().getName().endsWith("String")) {
insertStmt.setString(columnIndex++,(String)columnValue);
} else if (columnValue.getClass().getName().endsWith("Timestamp")) {
insertStmt.setTimestamp(columnIndex++,(Timestamp)columnValue);
}
}
}
insertStmt.executeUpdate();
if (dbType.startsWith("Microsoft SQL Server")) {
ResultSet nextValRS = executeQuery(connection, "select @@identity");
long nextVal = nextValRS.getLong(1);
newKey = nextVal;
}
return newKey;
}
}
