Fetch data from IBM AS400

The script demonstrates how to fetch data from IBM AS400 and save it in Active Directory. It queries the AS400 for a list of associates with the same last name as the selected user. Using the query results, the script then tries to match the specific user by using the last name, month of birth, day of birth and the last three digits of the Social Insurance Number (SIN). If a match is found, the script assign the Employee ID fetched from the AS400 to the user. The MOB, DOB and SIN are stored in Adaxes custom attributes of the user account.

To get Employee IDs from the AS400 database, you can, for example, run the script automatically upon creation of a new user account in AD.

Edit Remove

PowerShell

Import-Module Adaxes

# Get last name, DOB, MOB and the last 3 digits of the SIN from AD
$adLastname = "%lastname%".ToUpper()

[string]$adDOB = "%adm-CustomAttributeInt2%"
if ($adDOB.Length -lt 2) 
{
    $adDOB = "0" + $adDOB
}

[string]$adMOB = "%adm-CustomAttributeInt1%"
if ($adMOB.Length -lt 2) 
{
    $adMOB = "0" + $adMOB
}

[string]$adSIN = "%adm-CustomAttributeInt3%"
if ($adSIN.Length -lt 3) 
{
    $adSIN = "0" + $adSIN
}

$tmpEmpID = $adMOB + $adDOB + $adSIN
$Context.LogMessage("tmpEmpID is $tmpEmpID", "Information")  

# Assembly name from \\HKCR\Installer\Assemblies\Global
$an = 'IBM.Data.DB2.iSeries,Version="12.0.0.0",Culture="neutral",FileVersion="13.0.10.0",ProcessorArchitecture="MSIL",PublicKeyToken="9CDB2EBFB1F93A26"'
Add-Type -AssemblyName $an

try
{
    # Connection string
    $connectionString = 'Data Source=*********;User ID=******;Password=*********'
    $connection = New-Object IBM.Data.DB2.iSeries.iDB2Connection($connectionString)
    $connection.Open()
    
    $command = $connection.CreateCommand()
    #YAAN8 = EmployeeID
    #YAALPH = Name
    #YADOB = Date of Birth
    #YAPAST = Pay Status
    $command.CommandText = "SELECT TRIM(yaan8) AS YAAN8, TRIM(yaalph) AS YAALPH, MONTH(CHAR(yadob+1900000)) AS MONTH, DAY(CHAR(yadob+1900000)) AS DAY, TRIM(yassn) AS YASSN, TRIM(yapast) AS YAPAST FROM CRPDTA.F060116 WHERE YAALPH LIKE UPPER('$adLastname%%')"

    # Connect to the database
    $dataAdapter = New-Object IBM.Data.DB2.iSeries.iDB2DataAdapter($command)
    $dataSet = New-Object System.Data.DataSet
    
    $as400ReturnedRecordCount = $dataAdapter.Fill($dataSet)
    $Context.LogMessage("AS400 Returned $as400ReturnedRecordCount records", "Information")
    
    
    foreach ($row in $dataSet.Tables[0].Rows) 
    {
        $as400EmployeeID = [string]$row.Item('YAAN8')
        $tmpName = [string]$row.Item('YAALPH')
        $as400LName = $tmpName.split(",") 
        $as400LName = $as400LName[0]
        
        $tmpSIN = [string]$row.Item('YASSN')
        if ($tmpSIN.Length -gt 0) 
        {
            $catSIN = $tmpSIN.SubString($tmpSIN.Length-3)
        }
        
        $monthOB = [string]$row.Item('MONTH')
        if ($monthOB.Length -lt 2) 
        {
            $monthOB = "0" + $monthOB
        }
        
        $dayOB = [string]$row.Item('DAY')
        if ($dayOB.Length -lt 2) 
        {
            $dayOB = "0" + $dayOB
        }
        
        $joinedString = $monthOB + $dayOB + $catSIN
        
        if ($tmpEmpID -eq $joinedString -AND $adLastname -eq $as400LName) 
        {
            $Context.LogMessage("Found a match to AD Account %username% with AS400 $tmpName ($as400EmployeeID)", "Information")
            try
            {
                Set-AdmUser -Identity %username% -EmployeeID $AS400EmployeeID -AdaxesService localhost -ErrorAction Stop
            }
            catch
            {
                if ($_.Exception.ErrorCode -ne 0x8000000A)
                {
                    Write-Error $_.Exception.Message
                }
            }
        }
    } 
}
finally
{
    if ($dataSet) { $dataSet.Dispose() }
    if ($command) { $command.Dispose() }
    if ($connection) { $connection.Close() }
}

Script Repository

Got questions?