locked
Compare all the values in a column in a csv against the contents of another csv and output to a third file RRS feed

  • Question

  • First of all, let me apologise for the horrific code contained below which badly needs repairing.

    Secondly let me explain what I want to do. The file named admingroups.csv contains a single column with a header of ‘DistinguishedName’ and contains a list of admin accounts. The file users.csv contains a list of staff in a team with four columns of name,samaccountname,userprincipalname, and ‘memberof’ (this last column contains the groups of which each user is a member, and each individual’s groups are all listed on a separate row – I reformatted the original AD export which gave each user just one row for all information  with all their groups stuffed into a single cell. What I want powershell to do is to check through users.csv line by line and compare every group listed against the list of admin groups within admingroups.csv. I wish to output the data in first three columns of users.csv into a new file along with manipulating the fourth column data so each group name outputs to a new column called ‘AdmGroup’ if the value matches any admin group name in admingroups.csv (if it does not it should output to the ‘StdGroup’ column). This is not working, and the columns AdmGroup and StdGroup are just filled with ‘False’ and ‘True’ values. 

    I *think* I need to do the following:  change the format so I can output text and make the script specifically output group names; and ensure that the pipeline queries just one value at a time rather than all at once. I’d love some help with this so I can fix this and remember for the next time too.

    $team='teamname'

    $groupslist = import-csv -Path C:\temp\admingroups.csv

    $mainlist = import-csv C:\temp\Teams\$team\users.csv

     

    $mainlist | ForEach-Object {
            if($match = "$mainlist.memberof" -eq "$_($groupslist.DistinguishedName)"){
                $_
            }else{($nomatch = "$mainlist.memberof" -ne "$_($groupslist)")
                $_
            }
            }| Select-Object Name, SamAccountName,UserPrincipalName,@{n='AdmnGroup';e={$match}},@{n='StdGroup';e={$nomatch}} |

             Export-Csv "C:\temp\Results\$team.groupmembers.csv"

    Wednesday, October 10, 2018 12:59 AM

Answers

  • You can try the below which I believe will get your desired results.  You can replace the $admgroups with an Import-CSV or Get-Content if you prefer to not hard code the list of groups into the PS script.

    Basically it's just going to count the number of "admin" groups a user belongs to and tag then as "admin" for any value greater than zero.  Another approach would be to list out all members of the admin groups and remove duplicates.

    $report=@()
    $users = Get-ADuser -properties * -filter * -server yourdomain.com | select name,samaccountname,userprincipalname,memberof
    $admgroups = @('CN=AdmGroup1,DC=yourdomain,DC=com', 'CN=AdmGroup2,DC=yourdomain,DC=com')
    
    
    ForEach ($user in $users)
    
    {
        $tmpObj = New-Object -TypeName Psobject
        $tmpObj | Add-Member -MemberType NoteProperty -Name Name -Value $user.name
        $tmpObj | Add-Member -MemberType NoteProperty -Name SAM -Value $user.samaccountname
        $tmpObj | Add-Member -MemberType NoteProperty -Name UPN -Value $user.userprincipalname
    
        [int]$count='0'
        
            ForEach ($admgroup in $admgroups) 
            {
            if ($user.memberof -match $admgroup) {$count++}     
            } 
    
            if ($count -gt 0) {$tmpObj | Add-Member -MemberType NoteProperty -Name GroupClass -Value 'AdmGroup'}
            elseif ($count -eq 0) {$tmpObj | Add-Member -MemberType NoteProperty -Name GroupClass -Value 'StdGroup'}
    
        $report+=$tmpObj
    }
    
    $report | Export-CSV .\AdmReport.csv -NoTypeInformation

    • Marked as answer by GoranV19 Wednesday, October 10, 2018 6:43 PM
    Wednesday, October 10, 2018 2:04 PM

All replies

  • You cannot use -eq and -ne with a collection.

    To match you need to "look up" the item in the collection.

    You cannot place quotes around an object's property.  It will not work.  Quotes are neve needed here and will never work. Remove them.

    To find a member of a collection.

    if($item -in $collection){ 'found')}else{'not found'}


    \_(ツ)_/

    Wednesday, October 10, 2018 1:48 AM
  • You can try the below which I believe will get your desired results.  You can replace the $admgroups with an Import-CSV or Get-Content if you prefer to not hard code the list of groups into the PS script.

    Basically it's just going to count the number of "admin" groups a user belongs to and tag then as "admin" for any value greater than zero.  Another approach would be to list out all members of the admin groups and remove duplicates.

    $report=@()
    $users = Get-ADuser -properties * -filter * -server yourdomain.com | select name,samaccountname,userprincipalname,memberof
    $admgroups = @('CN=AdmGroup1,DC=yourdomain,DC=com', 'CN=AdmGroup2,DC=yourdomain,DC=com')
    
    
    ForEach ($user in $users)
    
    {
        $tmpObj = New-Object -TypeName Psobject
        $tmpObj | Add-Member -MemberType NoteProperty -Name Name -Value $user.name
        $tmpObj | Add-Member -MemberType NoteProperty -Name SAM -Value $user.samaccountname
        $tmpObj | Add-Member -MemberType NoteProperty -Name UPN -Value $user.userprincipalname
    
        [int]$count='0'
        
            ForEach ($admgroup in $admgroups) 
            {
            if ($user.memberof -match $admgroup) {$count++}     
            } 
    
            if ($count -gt 0) {$tmpObj | Add-Member -MemberType NoteProperty -Name GroupClass -Value 'AdmGroup'}
            elseif ($count -eq 0) {$tmpObj | Add-Member -MemberType NoteProperty -Name GroupClass -Value 'StdGroup'}
    
        $report+=$tmpObj
    }
    
    $report | Export-CSV .\AdmReport.csv -NoTypeInformation

    • Marked as answer by GoranV19 Wednesday, October 10, 2018 6:43 PM
    Wednesday, October 10, 2018 2:04 PM
  • Thank-you so much for responding. I was wondering a bit concerned about detaching it from AD and pointing it at previously completed data extracts, but with a few tweaks it worked absolutely fine.

    Wednesday, October 10, 2018 6:43 PM