Relevant Reconciliation Database Tables and Queries
Table Of Contents
1.Introduction
2.Reconciliation table schema
2.1RECONCILIATION table
2.2RECONCILIATION_INFO table
3Other tables relevant to reconciliation
3.1PROCESS table
3.2ACTIVITY table
3.3SERVICE table
4.1How to identify the type of service / reconciliation
5Sample Queries
5.1 How to identify last reconciliation for a given service DN?
5.2Account Reconciliation
5.2.1Getting Summary of Reconciliation
5.2.2 Getting Count of entries from RECONCILIATION_INFO table
5.2.3Getting entries of a particular operation from RECONCILIATION_INFO table
5.3Person Reconciliation with workflow
5.3.1Identifying Reconciliation with workflow
5.3.2Getting Summary of Reconciliation
5.3.3 Getting Count of entries from RECONCILIATION_INFO table
5.3.4Getting entries of a particular operation from RECONCILIATION_INFO table
5.4Person Reconciliation without workflow
5.4.1Getting Summary of Reconciliation
5.4.2 Getting Count of entries from RECONCILIATION_INFO table
5.4.3Getting entries of a particular operation from RECONCILIATION_INFO table
6Generating CSV Report from Query Result Set
Glossary of Important Terms
1.Introduction
This document describes the different reconciliation tables RECONCILIATION and RECONCILIATION_INFO. It describes how the two tables are used to query different information about reconciliation.
This document also describes how to make use of other ITIM tables like SERVICE, PROCESS, ACTIVITY etc for fetching data related to reconciliation(s).
Also, the meanings of various character codes used in various tables and their semantics are documented.
Finally, the document provides various SQL queries which can be used with any reporting tool to query reconciliation data.
The target audience of this document should have knowledge of databases, SQL, ITIM database schema, reconciliation process and processes and activities.Knowledge of any reporting tool and/or a programming language like Java would be helpful.
2.Reconciliation table schema
There are two tables which store the data related to reconciliation in the system RECONCILIATION and RECONCILIATION_INFO. The data in these two tables is populated by remoteservices component as the last step of reconciliation process. Data synchronization is not a prerequisite for populating data in these tables.
2.1RECONCILIATION table
This table contains the summary of the information for reconciliation on various service instances. The table contains an entry for all completed reconciliation on various service instances.
Table 1
Column Name / Description / Data TypeRECONID (PK) / An Identifier that identifies a reconciliation uniquely. This id is unique for every reconciliation of every service. / varchar
ServiceDN / The DN of the service for which this entry is recorded / varchar
ProcessedAccounts / Total number of accounts processed during reconciliation. / int
LocalAccounts / Total number of new local accounts created. / int
TimUserAccounts / The number of processed accounts which belong to users in ITIM. / int
PolicyViolations / The number of policy violations of accounts for this service instance. / int
STARTED / Start time of the reconciliation / datetime
COMPLETED / End time of the reconciliation / datetime
ACTIVITY_ID / Activity ID of the reconciliation activity. It stores the status of the reconciliation activity. / bigint
2.2RECONCILIATION_INFO table
This table will contain the details of the reconciliation on various service instances. For example, this table contains all the accounts or person entries reconciled during the reconciliation. This table does not have primary key constraints.
Table 2
Column Name / Description / Data TypeRECONID / An Identifier that identifies a reconciliation uniquely. This column is used to take join with RECONCILIATION table. / varchar
AccountID / The ID of an entry (for example an account) / varchar
POLICYCOMPLIANCESTATUS / Policy Compliance status of this processed entry. / varchar
UserName / Name of the user associated with AccountID / varchar
Operation / The operation performed for this entry for this service instance. Possible values for an account entry are NL, NO, SA, DA etc. These are codes which stand for various account operations like “New Local”, “New Orphan”, “Suspended Account” , “Deprovisioned Account” etc. / varchar
Remarks / Contains the reason for policy violations for accounts with policy violations and list of attributes in case of modified accounts. / varchar
HANDLE / This column stores the ACTIVITY.ID values with which this person entry is associated when there is a workflow associated with reconciliation. / int
The OPERATION column contains values corresponding to reconciliation status of the reconciled entry.
Following table lists distinct values for OPERATION column and their semantics for Account reconciliation (described later).
Table 3
Value / Meaning / DescriptionNO / New Orphan account / A new orphan account was created for this entry.
NL / New Local account / A new local account was created for this entry.
MA / Modified Account / This account was modified during reconciliation.
RL / Removed Local account / This account was removed from ITIM during reconciliation.
FP / Failed Policy / This account failed policy evaluation during reconciliation.
SA / Suspended Account / This account was suspended during reconciliation due to policy enforcement.
DA / Deprovisioned Account / This account was de-provisioned during reconciliation due to policy enforcement.
Following table lists distinct values for OPERATION column and their semantics for Person reconciliation (described later).
Table 4
AP / Add Person / This person was added during reconciliation.MP / Modify Person / This person was modified during reconciliation.
FAP / Failed Add Person / This person entry failed addition during reconciliation.
FMP / Failed Modify Person / This person entry failed modify during reconciliation.
UP / Unchanged Person / This person entry was unchanged during reconciliation.
PAP / Pending Add Person / This person entry is pending addition due to a workflow activity.
PMP / Pending Modify Person / This person entry is pending modification due to a workflow activity.
Following table lists distinct values for POLICYCOMPLIANCESTATUS column and their semantics.
Table 5
Value / Meaning0 / Account Compliance Unknown
1 / Account is Compliant
2 / Unauthorized
3 / Constraint Violation
3Other tablesrelevant to reconciliation
To generate useful reports some other tables in ITIM database are required to be used. These tables store information related to reconciliation entries and services. These tables are PROCESS, ACTIVITY, and Service. In subsequent sections various ITIM tables are discussed. Here only those columns of tables are discussed which are relevant with respect to reconciliation.
3.1PROCESS table
The PROCESS table stores all the pending, running, and historical requests submitted to the Tivoli Identity Manager workflow. Each request is represented as a process.
PROCESS table stores information about the reconciled entries when a workflow is attached with the reconciliation process. For example, when doing HR Feed (person recon) type of recon in ITIM Express 4.6, a Personal Password account is created for every reconciled person entry. This is done through a workflow. Some of these activities may complete after reconciliation process. This table stores this information and its status at a given point of time.
Following are important columns in PROCESS table with respect to reconciliation process.
Table 6
Column Name / Description / Data TypeID / Process ID number. / numeric
RESULT_SUMMARY / This column contains character codes to specify the result of the process. Like SS, SF, SW, PE, / character
3.2ACTIVITY table
The ACTIVITY table contains records of each workflow process's execution flow.
Table 7
Column Name / Description / Data TypeID / Activity ID number. / numeric
PROCESS_ID / Activity’s Process ID number. / numeric
DEFINITION_ID / Activity’s Definition Identifier / character
RESULT_SUMMARY / Activity’s result summary code. Some important values are:
SUBMITTED(RS)
SUCCESS (SS)
FAILED (SF)
WARNING (SW)
PENDING (PE)
RESULT_DETAIL / Detailed results information for the activity / long character
3.3SERVICE table
This table is dropped, recreated and populated as part of Data Synchronization. Therefore, it is mandatory that data synchronization is performed before using any SQL queries/report which makes use of this table. This contains the details of all the services in the system. The columns in this table are configurable using schema designer functionality of ITIM. Some important columns of this table are:
Table 8
Column Name / Description / Data TypeDN / DN of the service. / character
TARGETCLASS / Targetclass of the service. / character
ERSERVICENAME / Name of the service. / character
SERVICETYPE / Type of the service. / character
This table can be used to get the service name of the service whose reconciliation data is being queried. The RECONCILIATION.SERVICEDN column should be joined with Service.DN column and then the service name can be obtained.
Query 1
Ex: Select Service.erservicename
From Service, RECONCILIATION
Where Service.DN = RECONCILIATION.SERVICEDN
And RECONCILIATION.SERVICEDN = ‘?’
NOTE: ? in the above query is a place holder for values to be provided by user before executing the query. Similar convention is used at many places in this document.
4Types of Reconciliation
There are two categories of reconciliation (and services with respect to reconciliation) in ITIM.
- Person Reconciliation: These are also called as Feed types of reconciliation. And the services are referred to as Feed type of services. These services are typically CSV feed, DSML services, etc
- Account Reconciliation: These types of reconciliation typically fetch data from various resources like Winlocal machine, databases like Oracle, WinAd machines etc.
4.1How to identify the type of service / reconciliation
Following query can be used to identify which category a particular service belongs to:
Query 2
SELECT TARGETCLASS
FROM SERVICE
WHERE SERVICE.DN =’?’
Now, if the targetclass returned by above query is one of the following, then the service is of Person Reconciliation type else it is of Account Reconciliation type.
List of Person Reconciliation service targetclasses:
- ercsvfeed
- eradjndifeed
- erjndifeed
- erDSMLInfoService
- erDSML2Service
5Sample Queries
This section contains some sample queries that are used in out-of-box reconciliation report for querying the database while generation Reconciliation Statistics report.
5.1 How to identify last reconciliation for a given service DN?
To identify last reconciliation for a given Service DN following query can be used.
Query 3
SELECT COMPLETED, RECONID, ACTIVITY_ID
FROM RECONCILIATION
WHERE SERVICEDN =?
AND COMPLETED = (SELECTMAX(COMPLETED) FROM RECONCILIATION WHERE SERVICEDN =?)
The given service DN should be replace “?” in above query.
Following query will give status of reconciliation.
Query 4
SELECT PROCESS.RESULT_SUMMARY
FROM PROCESS
WHERE PROCESS.ID =
(SELECT ACTIVITY.PROCESS_ID
FROM ACTIVITY
WHERE ACTIVITY.ID =?)
Provide ACTIVYT_ID in place of “?” obtained from query 3.
5.2Account Reconciliation
A number of queries are required to be run in order to fetch data related to account reconciliation. This section describes these queries in detail.
5.2.1Getting Summary of Reconciliation
Following query can be used to get a summary of Account reconciliation. The summary includes data like, No of accounts processes, No of new local accounts, No of orphan accounts, No of accounts with policy violations etc.
Query 5
SELECT PROCESSEDACCOUNTS, TIMUSERACCOUNTS, POLICYVIOLATIONS, LOCALACCOUNTS
FROMRECONCILIATION
WHERE RECONID = ‘?’
5.2.2Getting Count of entries from RECONCILIATION_INFO table
While generating report, it may be desirable to know the number of entries present. The number of accounts can be grouped based on account operation performed during reconciliation.
For example, to know the number of New Local(NL)accounts created during reconciliation, following query can be executed.
Query 6
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
AND RECONCILIATION_INFO.OPERATION = ‘NL’
Refer to Table 3 for more OPERATION types.
5.2.3Getting entries of a particular operation from RECONCILIATION_INFO table
Once the number of entries of a particular operation type is found, another query can be executed to fetch all the entries of a particular operation type as follows:
Query 7
SELECT RECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
ANDRECONCILIATION_INFO.OPERATION = ‘NL’
ORDERBY RECONCILIATION_INFO.ACCOUNTID
Similar queries can be executed by changing OPERATION value (refer to table 3) to get all entries of other operation types.
5.3Person Reconciliation with workflow
There are two types of Person reconciliation in ITIM. This section describes how to get reconciliation related data of a Person reconciliation which has a workflow associated with it.
5.3.1Identifying Reconciliation with workflow
All the reconciliation which has a workflow associated with it can be identified using following SQL query. In essence, if RECONCILIATION_INFO.HANDLE has a value other than -1, then that reconciliation uses workflow.
Query 8
SELECT COUNT(HANDLE)
FROM RECONCILIATION_INFO
WHERE (RECONID = ‘?’)
AND (HANDLE > -1)
If the value returned by above query is more than 0, then the reconciliation (identified by reconid specified at ‘?’) uses workflow. Else, there is no workflow associated with the reconciliation.
5.3.2Getting Summary of Reconciliation
To get total number of person entries processed by a specific reconciliation following query can be used.
Query 9
SELECT PROCESSEDACCOUNTS
FROMRECONCILIATION
WHERE RECONID = ‘?’
5.3.3Getting Count of entries from RECONCILIATION_INFO table
Following query can be used to get count of entries which are pending addition due to workflow.
Query 10
SELECT COUNT (ACCOUNTID)
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY='SS')
AND (RECONID = '?')
AND ((OPERATION='PAP') AND (ACTIVITY.DEFINITION_ID = 'CREATEPERSON'))
Similarly, following query can be used to get count of entries which are pending modification due to workflow.
Query 11
SELECT COUNT (ACCOUNTID)
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY='SS')
AND (RECONID = '?')
AND ((OPERATION='PMP') AND (ACTIVITY.DEFINITION_ID = ‘MODIFYPERSON’))
Following set of queries can be used to get count of entries which failed during person addition. Addition of the counts returned by the two queries will give total number of persons failed during addition.
Query12
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY'SS')
AND (RECONID = '?')
AND ((OPERATION='PAP') AND (ACTIVITY.DEFINITION_ID = 'CREATEPERSON'))
Query13
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO, PROCESS
WHERE (PROCESS.ID = RECONCILIATION_INFO.HANDLE)
AND (PROCESS.RESULT_SUMMARY>'SS')
AND (RECONCILIATION_INFO.RECONID = '?')
AND PROCESS.ID NOT IN (SELECT ACTIVITY.PROCESS_ID FROM ACTIVITY WHERE ACTIVITY.DEFINITION_ID = 'CREATEPERSON')
Similarly, following set of queries can be used to get count of entries which failed during person modification. Addition of the counts returned by the two queries will give total number of persons failed during modification.
Query 14
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY'SS')
AND (RECONID = '?')
AND ((OPERATION='MAP') AND (ACTIVITY.DEFINITION_ID = 'MODIFYPERSON'))
Query 15
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO, PROCESS
WHERE (PROCESS.ID = RECONCILIATION_INFO.HANDLE)
AND (PROCESS.RESULT_SUMMARY>'SS')
AND (RECONCILIATION_INFO.RECONID = '?')
AND PROCESS.ID NOT IN (SELECT ACTIVITY.PROCESS_ID FROM ACTIVITY WHERE ACTIVITY.DEFINITION_ID = 'MODIFYPERSON')
5.3.4Getting entries of a particular operation from RECONCILIATION_INFO table
Following query can be used to get entries which are pending addition due to workflow.
Query 16
SELECT ACCOUNTID
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY='SS')
AND (RECONID = '?')
AND ((OPERATION='PAP') AND (ACTIVITY.DEFINITION_ID = 'CREATEPERSON'))
Similarly, following query can be used to entries which are pending modification due to workflow.
Query 17
SELECT ACCOUNTID
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY='SS')
AND (RECONID = '?')
AND ((OPERATION='PMP') AND (ACTIVITY.DEFINITION_ID = ‘MODIFYPERSON’))
Following set of queries can be used to get entries which failed during person addition. Addition of the results returned by the two queries will give total persons failed during addition.
Query 18
SELECT RECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY'SS')
AND (RECONID = ‘?’)
AND ((OPERATION='PAP') AND (ACTIVITY.DEFINITION_ID = 'CREATEPERSON'))
Query 19
SELECT RECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO, PROCESS
WHERE (PROCESS.ID = RECONCILIATION_INFO.HANDLE)
AND (PROCESS.RESULT_SUMMARY>'SS')
AND (RECONCILIATION_INFO.RECONID = '?')
AND PROCESS.ID NOT IN (SELECT ACTIVITY.PROCESS_ID FROM ACTIVITY WHERE ACTIVITY.DEFINITION_ID = 'CREATEPERSON')
Similarly, following set of queries can be used to entries which failed during person modification. Addition of the results returned by the two queries will persons failed during modification.
Query 20
SELECT RECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO, ACTIVITY
WHERE (ACTIVITY.PROCESS_ID = RECONCILIATION_INFO.HANDLE)
AND (ACTIVITY.RESULT_SUMMARY'SS')
AND (RECONID = '?')
AND ((OPERATION='MAP') AND (ACTIVITY.DEFINITION_ID = 'MODIFYPERSON'))
Query 21
SELECT RECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO, PROCESS
WHERE (PROCESS.ID = RECONCILIATION_INFO.HANDLE)
AND (PROCESS.RESULT_SUMMARY>'SS')
AND (RECONCILIATION_INFO.RECONID =’?’)
AND PROCESS.ID NOT IN (SELECT ACTIVITY.PROCESS_ID FROM ACTIVITY WHERE ACTIVITY.DEFINITION_ID = 'MODIFYPERSON')
5.4Person Reconciliation without workflow
There are two types of Person reconciliation in ITIM. This section describes how to get reconciliation related data of a Person reconciliation which doesn’t have a workflow associated with it.
5.4.1Getting Summary of Reconciliation
To get total number of person entries processed by a specific reconciliation following query can be used.
Query 22
SELECT PROCESSEDACCOUNTS
FROMRECONCILIATION
WHERE RECONID = ‘?’
5.4.2 Getting Count of entries from RECONCILIATION_INFO table
Following query can be used to get count of entries which added new persons, modified old persons or failed.
Query 23
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
AND RECONCILIAITION_INFO.OPERATION = ‘AP’
Refer to Table 4 for more OPERATION types.
More than one OPERATION types can be combined as follows:
Query 24
SELECT COUNT (RECONCILIATION_INFO.ACCOUNTID)
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
AND (RECONCILIATION_INFO.OPERATION = ‘FAP’
OR RECONCILIATION_INFO.OPERATION = ‘FMP’)
Above query will give count of all the failed entries during person addition and person modification during reconciliation.
5.4.3Getting entries of a particular operation from RECONCILIATION_INFO table
Once the number of entries of a particular operation type is found, another query can be executed to fetch all the entries of a particular operation type as follows:
Query 25
SELECTRECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
ANDRECONCILIATION_INFO.OPERATION = ‘AP’
Refer to Table 4 for more OPERATION types.
More than one OPERATION types can be combined as follows:
Query 26
SELECTRECONCILIATION_INFO.ACCOUNTID
FROM RECONCILIATION_INFO
WHERE RECONCILIATION_INFO.RECONID = ‘?’
AND (RECONCILIATION_INFO.OPERATION = ‘FAP’
OR RECONCILIATION_INFO.OPERATION = ‘FMP’)
Above query will give all the failed entries during person addition and person modification during reconciliation.
6Generating CSV Report from Query Result Set
Following section gives pseudo-code for parsing a JDBC result set and generating a Comma Separated Value report using some of the queries described above.
// Obtain a JDBC connection object to ITIMDB
Connection connection = DriverManager.getConnection(…);
String reconID = “actual recon id”// obtained using other queries
//This will summary of the reconciliation
String sqlQuery = “SELECT PROCESSEDACCOUNTS, TIMUSERACCOUNTS,
POLICYVIOLATIONS, LOCALACCOUNTS
FROM RECONCILIATION
WHERE RECONID = ‘reconid obtained earlier’”
String final COMMA = “,”;
String final NEWLINE = “\n”;
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sqlQuery);
StringBuffer sb = new StringBuffer();
int processedAccounts = 0;
int timuserAccounts = 0;
int policyViolations = 0;
int localAccounts = 0;
// Add column headers to CSV report
sb.append(“PROCESSEDACCOUNTS”);
sb.append(COMMA);
sb.append(“TIMUSERACCOUNTS”);
sb.append(COMMA);
sb.append(“POLICYVIOLATIONS”);
sb.append(COMMA);
sb.append(“LOCALACCOUNTS”);
sb.append(NEWLINE);
//Parse the ResultSet and create a CSV report
while (resultSet.next()) {