PSD1183 – Create result grid in PIM from a SQL query

Prerequisites

PhenixID Identity Manager (PIM) 5.6.2 or later.

Overview

Use Case 1

In PSD1182 you send a query from PIM to SQL to receive a list of users. PIM will map those users to LDAP and remove all users that it can’t map.
For the remaining users, PIM will collect data of your choice. What data to collect is what this PSD will instruct.

Use Case 2

Using a normal predefined search, you want to add an extra column with information from a SQL database.

Configuration

With a number of polices you specify e.g. the SQL server and SQL query. (see below the different policies)

Add filter to PIM file system

1. Open file system
2. Add file in https://files.phenixid.se/s/3yMHd4ErLMyb4FF to drive:\..\PhenixID\IM\customer\extension\class\psd
3. Restart PIM.

Policies / Tab Parameters

The policies below could either be used as policies in DSEditor.properties or as Tab Parameters in a form.

Below is the policies/tab parameters available. All parameters are available to use either as policies or parameter. Below is all the policies/parameters, an explanation and also where you probably would like to add the policy/parameter.

# Activate grid result component
# Probably added as a policy in DSEditor.properties
CUSTOM_PD_SEARCH_GRID_RESULT=psd.SearchInSqlPDSearchGridResult

# What data should be added to the result set for each user? Left of | is SQL column, right 
# is display value in PIM result set
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchGridResult.extraColumns=fornamn|Förnamn,efternamn|Efternamn,email|E-post,ansvarsnivåkod|Ansvarsnivåkod

# What SQL driver to use for PIM
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchGridResult.jdbc=net.sourceforge.jtds.jdbc.Driver

# Path to the SQL server including port and database name. The correct path is 
# decided by the driver used. Below is an example of the example driver above.
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchGridResult.url=jdbc:jtds:sqlserver://127.0.0.1:1433/your_database

# Login name to database
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchGridResult.username=sa

# Password for username
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchGridResult.pwd=password

# The SQL stored procedure
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchGridResult.query=[dbo].[example_spGetUsers]

# Attribute from the SQL result including the mapping value
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchGridResult.sqlKey=UserID

# Attribute in LDAP including the mapping value
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchGridResult.ldapKey=sAMAccountName

They way PIM is designed, PIM will need to do the query for each object. This means that searches with a large result set could take some time to finish and is therefore not recommended.

Mix LDAP data with SQL data in result set

If you like to have some attribut in the result set from LDAP and some from SQL the filter will simply add the value from psd.SearchInSqlPDSearchGridResult.extraColumns to the end after the LDAP display attribut filter.

Example

On the Predefined search you have following display attribute configured:
sAMAccountName|UserID,description|Description,mobile|Mobile

The SQL psd.SearchInSqlPDSearchGridResult.extraColumns policy has the following value:
email|E-post,ansvarsnivåkod|Ansvarsnivåkod

The result look like:

Best practice configuration

Since you might like to have different predefined search forms with different columns you can mix PIM policies with tab parameters.

If you for example like to have one predefined search with columns
AD_DATA_1,AD_DATA_2,SQL_DATA_1,SQL_DATA_2,SQL_DATA_3
and another with AD_DATA_1,AD_DATA_3,SQL_DATA_2,SQL_DATA_2,SQL_DATA_5
then you can add all SQL policies as DSEditor policies except the policy psd.SearchInSqlPDSearchGridResult.extraColumns which you add as a tab parameter in each predefined search from in order to differentiate the searches.