Outputting array into single cell in CSV

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

Viewing 6 reply threads
  • 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: 2
      Replies: 1697
      Points: 3,385
      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: 2
      Replies: 1697
      Points: 3,385
      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: 5
      Replies: 105
      Points: 263
      Helping Hand
      Rank: Contributor

      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!

Viewing 6 reply threads
  • The topic ‘Outputting array into single cell in CSV’ is closed to new replies.