I have a PowerShell script that works up to a certain point that imports two CSV files, CSV #1 and CSV #2, and creates a new array ($content3) with input from both CSV files. I need to populate the third column (Roles) of the new array with information from CSV #2 (ApprovedGroups) that has a corresponding Name that has a match in CSV #1 (realName). The following code is a sort of facsimile of the data I am actually working with, and the names are fictitious. The script runs, but I can't get the last logic loop to work properly. I have been working on the last logic loop for a while now, and have been running into a brick wall. Does anyone have any advice?
PowerShell Script:
# Import CSV files
$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"
# Print existing CSV arrays
$content1
$content2
#create new array
$content3 =@()
# Populate new array with initial information and values
$i = 0
while ($i -lt $content1.Length) {
$content3 += @([PSCustomObject]@{UserName = $content1[$i].username; Name = $content1[$i].realName; Roles = ""})
$i++
}
# Look for matching name from $content3 in $content2, and then populate the corresponding Groups column in $content3 with the corresponding ApprovedGroups in $content2
# Does not currently work
$j = 0
foreach ($row in $content3) {
while ($j -lt $content3.Length) {
if ($row[$j].Name -match $content2.Name) {$row[$j].Roles -eq $content2.ApprovedGroups ; break}
else {break}
$j++
} }
# Print new array thus far
$content3
CSV #1 ($content1):
userName realName
-------- --------
BDupree Dupree,Bob
BDupree_admin Dupree,Bob
BDupree_backup Dupree,Bob
SSmith Smith,Susan
SSmith_admin Smith,Susan
SSmith_backup Smith,Susan
TCarver Carver,Tom
TCarver_admin Carver,Tom
TCarver_backup Carver,Tom
JBoyer Boyer,Joan
HDega Dega,Hector
CSV #2 ($content2):
Name ApprovedGroups
---- --------------
Dupree,Bob Users,Administrators,Backup Operators
Smith,Susan Users,Administrators,Backup Operators
Carver,Tom Users,Administrators,Backup Operators
Boyer,Joan Users
Dega,Hector Users
Initial Results:
UserName Name Roles
-------- ---- ------
BDupree Dupree,Bob
BDupree_admin Dupree,Bob
BDupree_backup Dupree,Bob
SSmith Smith,Susan
SSmith_admin Smith,Susan
SSmith_backup Smith,Susan
TCarver Carver,Tom
TCarver_admin Carver,Tom
TCarver_backup Carver,Tom
JBoyer Boyer,Joan
HDega Dega,Hector
Desired Results (please note the below items are to help with doing an audit on all of the approved roles (groups) for a specific person, who may have more than one account the roles (groups) are spread over):
UserName Name Roles
-------- ---- ------
BDupree Dupree,Bob Users,Administrators,Backup Operators
BDupree_admin Dupree,Bob Users,Administrators,Backup Operators
BDupree_backup Dupree,Bob Users,Administrators,Backup Operators
SSmith Smith,Susan Users,Administrators,Backup Operators
SSmith_admin Smith,Susan Users,Administrators,Backup Operators
SSmith_backup Smith,Susan Users,Administrators,Backup Operators
TCarver Carver,Tom Users,Administrators,Backup Operators
TCarver_admin Carver,Tom Users,Administrators,Backup Operators
TCarver_backup Carver,Tom Users,Administrators,Backup Operators
JBoyer Boyer,Joan Users
HDega Dega,Hector Users
UPDATE1: Running the following code did not produce the desired results:
$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"
# create hash table to be used as a mapping table
$nameToIDMap = @{}
# add each row from the csv to the mapping table - use realName as key, userName as value
$content1 |ForEach-Object {
$nameToIDMap[$_.realName] = $_.userName
}
$content2 |Select-Object @{Name='UserName';Expression={$nameToIDMap[$_.Name]}},Name,@{Name='Roles';Expression='ApprovedGroups'}
$content2 | ForEach-Object {
[pscustomobject]@{
UserName = $nameToIDMap[$_.Name]
RealName = $_.Name
Roles = $_.ApprovedGroups
}
} |Export-Csv "C:\Users\blah\Desktop\Test\test3.csv" -NoTypeInformation
Running it resulted in the following, which is closer to what I want to do but it is not quite there:
UserName Name Roles
-------- ---- -----
BDupree_backup Dupree,Bob Users,Administrators,Backup Operators
SSmith_backup Smith,Susan Users,Administrators,Backup Operators
TCarver_backup Carver,Tom Users,Administrators,Backup Operators
JBoyer Boyer,Joan Users
HDega Dega,Hector Users
What I want to do is:
UserName Name Roles
-------- ---- ------
BDupree Dupree,Bob Users,Administrators,Backup Operators
BDupree_admin Dupree,Bob Users,Administrators,Backup Operators
BDupree_backup Dupree,Bob Users,Administrators,Backup Operators
SSmith Smith,Susan Users,Administrators,Backup Operators
SSmith_admin Smith,Susan Users,Administrators,Backup Operators
SSmith_backup Smith,Susan Users,Administrators,Backup Operators
TCarver Carver,Tom Users,Administrators,Backup Operators
TCarver_admin Carver,Tom Users,Administrators,Backup Operators
TCarver_backup Carver,Tom Users,Administrators,Backup Operators
JBoyer Boyer,Joan Users
HDega Dega,Hector Users
I am currently trying the following code to get closer to what I want but I currently getting an error that I am working on troubleshooting:
$content1 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test1.csv"
$content2 = Import-Csv -Path "C:\Users\blah\Desktop\Test\test2.csv"
# create hash table to be used as a mapping table
$nameToGroupsMap = @{}
# add each row from the csv #2 to the mapping table - use Name as key, ApprovedGroups as value
$content2 |ForEach-Object {
$nameToGroupsMap[$_.Name] = $_.ApprovedGroups
}
$content1 | ForEach-Object {
[pscustomobject]@{
UserName = $_.userName
RealName = $_.realName
Roles = $nameToGroupsMap[$_.Name]
}
} | Export-Csv "C:\Users\blah\Desktop\Test\test3.csv" -NoTypeInformation
The error I am getting is:
Index operation failed; the array index evaluated to null.
At line:2 char:3
+ [pscustomobject]@{
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArrayIndex
Start by building a reverse mapping of Real Name -> List of User IDs from the data contained in the first CSV file.
For this you can use a hashtable:
# create hash table to be used as a mapping table
$nameToIDsMap = @{}
# add each row from the csv to the mapping table - use realName as key, userName as value
Import-Csv path\to\csv1.csv |ForEach-Object {
$nameToIDsMap[$_.realName] += @($_.userName)
}
Now we can use $nameToIDsMap as a lookup table for the user's possible accounts, and create a new object for each:
Import-Csv path\to\csv2.csv |ForEach-Object {
$personRecord = $_
$nameToIDsMap[$personRecord.Name] |ForEach-Object {
[pscustomobject]@{
UserName = $_
RealName = $personRecord.Name
Roles = $personRecord.ApprovedGroups
}
}
} |Export-Csv path\to\csv3.csv -NoTypeInformation