Hmmm … shame on me. I didn’t pay attention to the code you posted. I just read the ‘thanks’ and was glad.
I created some test data to play with and I think I got what you need.
$table = Import-Csv C:\table.csv -Header CERT_ID,USER_ID,ROLE_ID,CERTIFIED,CERTIFIED_BY,REMEDIATION_STATUS,COMMENTS
$certs = Import-Csv C:\certs.csv -Header CERT_ID,NAME,PERIOD,END_DATE,UPDATEUSER,UPDATEDATE
$roles = Import-Csv C:\roles.csv -Header ROLE_ID,ROLENAME,ROLEDESCRIPTION,CUSTOMPROPERTY1
$users = Import-Csv C:\users.csv -Header USER_ID,USERNAME,FIRSTNAME,LASTNAME,TITLE,PRIMARYEMAIL,OFFICENAME,MANAGER,LOCATION
$TableCerts = Foreach($Item in $table){
Foreach($cert in $certs){
If($Item.‘CERT_ID’ -eq $cert.‘CERT_ID’){
[PSCustomObject] @{
‘CERT_ID’ = $Item.‘CERT_ID’
‘USER_ID’ = $Item.‘USER_ID’
‘ROLE_ID’ = $Item.‘ROLE_ID’
‘CERTIFIED’ = $Item.‘CERTIFIED’
‘CERTIFIED_BY’ = $Item.‘CERTIFIED_BY’
‘REMEDIATION_STATUS’ = $Item.‘REMEDIATION_STATUS’
‘COMMENTS’ = $Item.‘COMMENTS’
‘NAME’ = $cert.‘NAME’
‘PERIOD’ = $cert.‘PERIOD’
‘END_DATE’ = $cert.‘END_DATE’
‘UPDATEUSER’ = $cert.‘UPDATEUSER’
‘UPDATEDATE’ = $cert.‘UPDATEDATE’
}
}
}
}
$TableCertRoles = foreach ($TableCert in $TableCerts) {
foreach ($role in $roles) {
if ($TableCert.‘ROLE_ID’ -eq $role.‘ROLE_ID’) {
[PSCustomObject] @{
‘CERT_ID’ = $TableCert.‘CERT_ID’
‘USER_ID’ = $TableCert.‘USER_ID’
‘ROLE_ID’ = $TableCert.‘ROLE_ID’
‘CERTIFIED’ = $TableCert.‘CERTIFIED’
‘CERTIFIED_BY’ = $TableCert.‘CERTIFIED_BY’
‘REMEDIATION_STATUS’ = $TableCert.‘REMEDIATION_STATUS’
‘COMMENTS’ = $TableCert.‘COMMENTS’
‘NAME’ = $TableCert.‘NAME’
‘PERIOD’ = $TableCert.‘PERIOD’
‘END_DATE’ = $TableCert.‘END_DATE’
‘UPDATEUSER’ = $TableCert.‘UPDATEUSER’
‘UPDATEDATE’ = $TableCert.‘UPDATEDATE’
‘ROLENAME’ = $role.‘ROLENAME’
‘ROLEDESCRIPTION’ = $role.‘ROLEDESCRIPTION’
‘CUSTOMPROPERTY1’ = $role.‘CUSTOMPROPERTY1’
}
}
}
}
$TableCertRoleUsers = foreach ($TableCertRole in $TableCertRoles) {
foreach ($user in $users) {
if ($TableCertRole.‘USER_ID’ -eq $user.‘USER_ID’) {
[PSCustomObject] @{
‘CERT_ID’ = $TableCertRole.‘CERT_ID’
‘USER_ID’ = $TableCertRole.‘USER_ID’
‘ROLE_ID’ = $TableCertRole.‘ROLE_ID’
‘CERTIFIED’ = $TableCertRole.‘CERTIFIED’
‘CERTIFIED_BY’ = $TableCertRole.‘CERTIFIED_BY’
‘REMEDIATION_STATUS’ = $TableCertRole.‘REMEDIATION_STATUS’
‘COMMENTS’ = $TableCertRole.‘COMMENTS’
‘NAME’ = $TableCertRole.‘NAME’
‘PERIOD’ = $TableCertRole.‘PERIOD’
‘END_DATE’ = $TableCertRole.‘END_DATE’
‘UPDATEUSER’ = $TableCertRole.‘UPDATEUSER’
‘UPDATEDATE’ = $TableCertRole.‘UPDATEDATE’
‘ROLENAME’ = $TableCertRole.‘ROLENAME’
‘ROLEDESCRIPTION’ = $TableCertRole.‘ROLEDESCRIPTION’
‘CUSTOMPROPERTY1’ = $TableCertRole.‘CUSTOMPROPERTY1’
‘USERNAME’ = $user.‘USERNAME’
‘FIRSTNAME’ = $user.‘FIRSTNAME’
‘LASTNAME’ = $user.‘LASTNAME’
‘TITLE’ = $user.‘TITLE’
‘PRIMARYEMAIL’ = $user.‘PRIMARYEMAIL’
‘OFFICENAME’ = $user.‘OFFICENAME’
‘MANAGER’ = $user.‘MANAGER’
‘LOCATION’ = $user.‘LOCATION’
}
}
}
}
$TableCertRoleUsers | Export-Csv -Path C:\results.csv -NoTypeInformation
I took your original posting as reference. Probably there is a more sofisticated way to do that - there always is one - but it does the job for now.