export-csv array in single line

Welcome Forums General PowerShell Q&A export-csv array in single line

Viewing 4 reply threads
  • Author
    Posts
    • #185981
      Participant
      Topics: 1
      Replies: 1
      Points: 16
      Rank: Member

      Hi everyone, I don't understand much about programming, I found on this site a script that I adapted to my needs but the output is not what I want.
      In the csv generated you can see
      :

      "SERVER","ADMINISTRATORS","DESCRIPTION"
      "H2OSRVTEST","Administrator","Development"
      "H2OSRVTEST","h2oadmin","Development"
      "H2OSRV1","Administrator","Production"
      "H2OSRV1","usertest","Production"
      "H2OSRV1","h2oadmin","Production"
      "RADIOLOGIA4","Administrator","Pc di test"
      "RADIOLOGIA4","user","Pc di test"
      "RADIOLOGIA4","Domain Admins","Pc di test"

      but I need to put all members of ADMINISTRATOR group in a single line for each Server, can someone help me? I have tried everything 🙁

      "SERVER","ADMINISTRATORS","DESCRIPTION"
      "H2OSRVTEST","Administrator" "h2oadmin","Development"
      "H2OSRV1","Administrator" "usertest" "h2oadmin","Development"
      "RADIOLOGIA4","Administrator" "user","Pc di test"

      $computers = Get-ADComputer -Filter * -Properties description -SearchBase "OU=server h2o,OU=sanatrix,DC=assa, DC=local" | select description,name
      $Results = @()
      
      Foreach ($Computer in $Computers){
      	   
      	$Name=$Computer.name
              $description=$Computer.description
      	write-host $Computer.description
      	$members =[ADSI]"WinNT://$Name/Administrators"
      	$members = @($members.psbase.Invoke("Members"))
      	$members | foreach {
      		$LocalAdmins = $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)    # Create a new object for the purpose of exporting as a CSV
      		$pubObject = new-object PSObject               
      		$pubObject | add-member -membertype NoteProperty -Name "SERVER" -Value $Name       
              $pubObject | Add-Member -membertype NoteProperty -name "ADMINISTRATORS" -Value $LocalAdmins
              $pubObject | Add-Member -MemberType NoteProperty -Name "DESCRIPTION" -Value $description
              $pubObject
      
      		# Append this iteration of our for loop to our results array.
      		$Results += $pubObject
      	}
      }
      
      $Results | Export-Csv -Path "C:\temp\ServerLocalAdmins.csv" -NoTypeInformation
      $Results = $Null
    • #186116
      Participant
      Topics: 3
      Replies: 68
      Points: 366
      Helping Hand
      Rank: Contributor

      Hi everyone, I don't understand much about programming

      I suggest, if you want to be able to solve these kinds of problems, that you make an effort to learn.

      So, let's talk about a potential solution to your problem. Here's one way you could adjust the output to look like what you want:

      $computers = Get-ADComputer -Filter * -Properties description -SearchBase "OU=server h2o,OU=sanatrix,DC=assa, DC=local" | select description,name
      $Results = @()
      
      Foreach ($Computer in $Computers){
      	   
      	$Name=$Computer.name
              $description=$Computer.description
      	write-host $Computer.description
      	$members =[ADSI]"WinNT://$Name/Administrators"
      	$members = @($members.psbase.Invoke("Members"))
      	$members | foreach {
      		$LocalAdmins = $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)    # Create a new object for the purpose of exporting as a CSV
      		$pubObject = new-object PSObject               
      		$pubObject | Add-member -membertype NoteProperty -Name "SERVER" -Value $Name       
              $pubObject | Add-Member -membertype NoteProperty -name "ADMINISTRATORS" -Value $LocalAdmins
              $pubObject | Add-Member -MemberType NoteProperty -Name "DESCRIPTION" -Value $description
              $pubObject
      
      		# Append this iteration of our for loop to our results array.
      		$Results += $pubObject
      	}
      }
      
      ## filter the Results array and collapse entries
      $FilteredResults =@()
      $tempObject = New-Object PSObject
      
      for ( $i=0; $i -lt $Results.Count; $i++ ) {
          $tempObject | Add-Member -MemberType NoteProperty -Name "SERVER" -Value $Results[$i].SERVER -Force
          $tempObject | Add-Member -MemberType NoteProperty -Name "ADMINISTRATORS" -Value $Results[$i].ADMINISTRATORS -ErrorAction SilentlyContinue
          $tempObject | Add-Member -MemberType NoteProperty -Name "DESCRIPTION" -Value $Results[$i].DESCRIPTION -Force
      
          if ( $tempObject.SERVER -eq $Results[$i+1].SERVER -and $tempObject.DESCRIPTION -eq $Results[$i+1].DESCRIPTION ) {
              $tempObject.ADMINISTRATORS = "$($tempObject.ADMINISTRATORS)" + ", $($Results[$i+1].ADMINISTRATORS)"
          } else {
              $FilteredResults += $tempObject
              $tempObject = New-Object PSObject
          }
      }
      
      $FilteredResults | Export-Csv -Path "C:\temp\ServerLocalAdmins.csv" -NoTypeInformation
      $Results = $Null
      $FilteredResults = $Null

      The $FilteredResults object will look like this:

      SERVER      ADMINISTRATORS                     DESCRIPTION
      ------      --------------                     -----------
      H2OSRVTEST  Administrator, h2oadmin            Development
      H2OSRV1     Administrator, usertest, h2oadmin  Production 
      RADIOLOGIA4 Administrator, user, Domain Admins Pc di test

      The section that I added takes an object from the $Results array and puts it into a temporary object (lines 28-31). It then compares that object to the next one in the array, looking to see if the SERVER and DESCRIPTION match (line 33). If they do match, it adds the ADMINISTRATORS for the next object to the temporary one (line 34). If they don't match, it adds the combined object ($tempObject) to the $FilteredResults array (line 36) and resets $tempObject for the next comparison (line 37).

      This definitely isn't the most efficient way to handle this, but I think it's relatively understandable. It is also non-destructive to your existing variables, meaning that it doesn't change the contents of the original $Results array if you should need to use it for something else.

      However, you should note that it joins all of the administrator names into a single value. PowerShell will no longer understand those names as being separate pieces of information. If you need to select individual administrator names from this information later, it will require more work.

    • #186161
      Participant
      Topics: 10
      Replies: 116
      Points: 451
      Helping Hand
      Rank: Contributor

      You can accomplish it with slightly less code and a bit more performance:

      # Get all computers from SearchBase
      $computers = Get-ADComputer -Filter * -Properties Description -SearchBase 'OU=server h2o,OU=sanatrix,DC=assa, DC=local' |
          Select-Object -Property Description, Name
      
      $results = foreach ($computer in $computers) {
      
          $computerName = $computer.Name
          $description  = $computer.Description
      
          $members = (([adsi]"WinNT://$computerName/Administrators,group").psbase.Invoke("Members"))
      
          $members | ForEach-Object {
              $localAdmins = $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)
      
              [pscustomobject]@{
                  SERVER         = $computerName
                  ADMINISTRATORS = $localAdmins
                  DESCRIPTION    = $description
              }
          }
      }
      
      $uniqueComputerNames = $results | Select-Object -Property SERVER -Unique
      
      foreach ($computerName in $uniqueComputerNames) {
          $computerName = $computerName | Select-Object -ExpandProperty SERVER
      
          [pscustomobject]@{
              SERVER         = $computerName
              ADMINISTRATORS = ($results | Where-Object { $_.SERVER -match $computerName } |
                  Select-Object -ExpandProperty Administrators) -join ', '
              DESCRIPTION    = ($results | Where-Object { $_.SERVER -match $computerName } |
                  Select-Object -ExpandProperty Description -Unique)
          }
      }

      As grokkit mentioned, the Administrators object is one long string, so you can't really do simple filtering as if they were individual objects. Pickup the MOL series – it'll do a lot for you.

    • #186209
      Participant
      Topics: 1
      Replies: 1
      Points: 16
      Rank: Member

      waooooo, guys, this is much more than what I needed, I don't know how to say thank you !!!!, not just because you gave me the solution (it was days that I tried, I work a lot and I have so little time to study 🙁 ), it gives me great joy because you share your knowledge without asking anything, I prayed and thanked for you, and if you find yourself passing through Italy in Rome, contact me and I will be happy to host you (there is little privacy, however, because my house it has no doors, I removed them all :))
      Forgive me for my English, I get help from google
      thanks again thanks thanks and thanks

    • #186227
      Participant
      Topics: 10
      Replies: 116
      Points: 451
      Helping Hand
      Rank: Contributor

      We all understand how crazy things can get, and that's why PowerShell in a Month of Lunches is perfect for getting started. Each chapter is designed to be completed during a work lunch. It's well-written, so it's easy to follow and understand. Going through these books will quickly enhance your skillset:

      Learn Windows PowerShell in a Month of Lunches, Third Edition
      https://www.manning.com/books/learn-windows-powershell-in-a-month-of-lunches-third-edition

      Learn PowerShell Scripting in a Month of Lunches
      https://www.manning.com/books/learn-powershell-scripting-in-a-month-of-lunches

      There are many other resources out there but these are a great start. They come in multiple formats (printed, pdf, kindle, and more).

Viewing 4 reply threads
  • You must be logged in to reply to this topic.