Outputting array into single cell in CSV

Welcome Forums General PowerShell Q&A Outputting array into single cell in CSV

This topic contains 7 replies, has 3 voices, and was last updated by

 
Participant
1 week, 6 days ago.

  • Author
    Posts
  • #180156

    Participant
    Topics: 2
    Replies: 9
    Points: 3
    Rank: Member

    I am trying to script a report and am struggling with a certain section.

    In my code below, $groupmembership is an array but I need that array to be entered into a single entry or cell in the resulting csv file. I tried doing a join with a carriage return but the output I'm getting looks like {@{name=user1}, @{name=user2}, @{name=user3} and then it truncates. I'm assuming the way I used join is joining the array heading, 'name' to each line of the array.

    I know its got to be something simple I'm missing or forgetting but I just cant figure it out. Any guidance would be appreciated.

    $rootshare = Get-ChildItem -Directory -Path "\\server\share" 
    $output = @()
    For($i = 1; $i -le $rootshare.Count; $i++){
        Write-Progress -Activity "Gathering Permissions" -PercentComplete ($i/$rootshare.Count*100)
        foreach($share in $rootshare){
            $acl = Get-Acl -Path $share.FullName
            foreach($access in $acl.Access){
                $secgroup = ($access.IdentityReference).ToString()
                $secgroup = $secgroup.Substring(10)
                $groupmembership = (Get-ADGroupMember -Identity $secgroup -Recursive | select name) -join "`n"
                $properties = [ordered]@{'This Share'=$share.FullName;'Grants these rights'=$Access.FileSystemRights;'To these groups'=$Access.IdentityReference;'Which consists of these members'=$groupmembership} 
                $output += New-Object -TypeName PSObject -Property $Properties 
            }
        }
    }
    $output | Out-GridView
  • #180180

    Participant
    Topics: 1
    Replies: 1547
    Points: 2,673
    Helping Hand
    Rank: Community Hero

    I'd recommend using a comma instead of a line break

    $groupmembership = (Get-ADGroupMember -Identity $secgroup -Recursive | Select-Object -ExpandProperty name) -join ','

    And you should use -ExpandProperty to turn the object into strings to be able to concatenate them with the comma.

  • #180204

    Participant
    Topics: 2
    Replies: 9
    Points: 3
    Rank: Member

    Thanks for the reply!

    Using the -ExpandProperty made it much easier to deal with. Using a comma to join the array isn't really an option as they need to be listed in the report this script will eventually create like:

    user1

    user2

    user3

    I'm getting closer but its still not right. When I run the script in ISE and am in debugger and get to this line:

    $groupmembership = (Get-ADGroupMember -Identity $secgroup -Recursive | Select-Object -ExpandProperty name) -join "`n"

    when I display the variable, it looks right, but when I load the output into the Out-GridView, I get the first 9 names and then it truncates. If i take the same output and run it through a Export-Csv, only the fist name is loaded.

     

  • #180207

    Participant
    Topics: 1
    Replies: 1547
    Points: 2,673
    Helping Hand
    Rank: Community Hero

    Using a comma to join the array isn't really an option as they need to be listed in the report

    hmmm ... I didn't get that but anyway – how should that look like if you have a bigger amount of members there. One cell will have the hight of tens or hundreds of lines while the other cells stay one line? You might think of another approach where you put another loop to list each individual member with its other attributes on indidual lines.

  • #180240

    Participant
    Topics: 4
    Replies: 76
    Points: 91
    Helping Hand
    Rank: Member

    This crappy thingie I've written does about what you are after

    
    $RootFolder = "x:"
    $identity = "Domain\*"
    $depth = 2
    $outputCSV = "h:blaa-$($depth+2).csv"
    $outputError = "h:\blaa-$($depth+2)-error.txt"
    
    $errorLog = @()
    #Look root folder ACL
    $FolderCollection = New-Object System.Collections.Generic.List[System.Object]
    $Folder = $RootFolder
    $currentDepth = $Folder.split("\").count
    
    foreach($access in (Get-Acl $Folder).Access) {
    
    $filerights = $access.FileSystemRights.ToString();
    $inheritanceFlg = $access.InheritanceFlags.ToString();
    
    if($inheritanceFlg -eq 'ContainerInherit') {
    $filerights = $filerights.replace('ReadAndExecute','ListDirectory');
    } #If
    
    if ($($access.IdentityReference.ToString()) -like "$identity") {
    $objProp = [ordered]@{
    folder = $folder
    group = $access.IdentityReference.ToString()
    Permission = $filerights
    inheritance = $access.IsInherited
    Users = (get-adgroupmember $($access.IdentityReference.ToString().split('\')[1]) | select -ExpandProperty name ) -join "; "
    }
    $CollectionObject = New-Object -TypeName PSObject -Property $ObjProp
    $FolderCollection.add($CollectionObject)
    } #If
    } #foreach($access in (Get-Acl $FOLDER).Access)
    
    #Go through all sub directories and take only non inherited permissions
    Get-ChildItem -Path $RootFolder -Directory -depth $depth | foreach {
    $Folder = $_.FullName
    $currentDepth = $Folder.split("\").count
    
    $accesses = try {
    Get-Acl $Folder -ErrorAction Stop
    }
    catch
    {
    $errorLog += $Folder
    }
    if ($accesses)
    {
    foreach($access in $accesses.Access) {
    
    if($($access.IsInherited.ToString()) -eq $false) {
    
    $filerights = $access.FileSystemRights.ToString();
    $inheritanceFlg = $access.InheritanceFlags.ToString();
    
    if($inheritanceFlg -eq 'ContainerInherit') {
    $filerights = $filerights.replace('ReadAndExecute','ListDirectory');
    
    } #If if($inheritanceFlg -eq 'ContainerInherit')
    
    if ($($access.IdentityReference.ToString()) -like "$identity") {
    $objProp = [ordered]@{
    folder = $folder
    group = $access.IdentityReference.ToString()
    Permission = $filerights
    depth = $currentDepth
    Users = (get-adgroupmember $($access.IdentityReference.ToString().split('\')[1]) | select -ExpandProperty name ) -join "; "
    }
    $CollectionObject = New-Object -TypeName PSObject -Property $ObjProp
    $FolderCollection.add($CollectionObject)
    $CollectionObject
    } #If ($($access.IdentityReference.ToString()) -like "$identity")
    
    } # if($($access.IsInherited) -eq $false)
    
    } #foreach($access in (Get-Acl $FOLDER).Access)
    } #if ($accesses)
    
    } #Get-ChildItem -Path $RootFolder -Directory -Recurse
    
    $FolderCollection | export-csv $outputCSV -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Verbose
    $errorLog | out-file $outputError
    
    • #180678

      Participant
      Topics: 2
      Replies: 9
      Points: 3
      Rank: Member

      This crappy thingie I've written does about what you are after

      At first glance, this looks spot on. Let me dissect it a bit and see if it is a better version of what I am trying to accomplish overall. Thanks!

  • #180675

    Participant
    Topics: 2
    Replies: 9
    Points: 3
    Rank: Member

    hmmm ... I didn't get that but anyway – how should that look like if you have a bigger amount of members there. One cell will have the hight of tens or hundreds of lines while the other cells stay one line? You might think of another approach where you put another loop to list each individual member with its other attributes on indidual lines.

    Yeah, I tried to explain that to the team requesting this but they said they'd deal with it so it is what it is.

  • #180732

    Participant
    Topics: 2
    Replies: 9
    Points: 3
    Rank: Member

    Actually came across this method which worked perfectly for me:

    $groupmembership = (Get-ADGroupMember -Identity $secgroup -Recursive | Select-Object -ExpandProperty name) -join [environment]::NewLine

    Thanks everyone!

You must be logged in to reply to this topic.