How can merge 3 cycle and export in one table-CodePudding

How can merge 3 cycle in one script. And export in one table

server	Name	email	Cluster	TcpTestSucceeded	PingSucceeded	RemoteAddress
Cell 1	Cell 22	Cell 11	Cell 2	Cell 21	Cell 13	Cell 27
Cell 3	Cell 44	Cell 33	Cell 4	Cell 41	Cell 33	Cell 47

Getting a list of email by DisplayName in AD

Import-Module ActiveDirectory

 

# path to file

$CSVpatch = "C:\temp\address.csv"

# Get

$result = Import-Csv -Path $CSVpatch -Delimiter ";" -Encoding Default | ForEach-Object {

    $server = $_.server

        Get-ADUser -Filter "DisplayName -eq '$($_.name)'" -Properties DisplayName, EmailAddress |

    Select-Object @{Name = 'server'; Expression = {$server}},

                  @{Name = 'name'; Expression = {$_.DisplayName}},

                  @{Name = 'email'; Expression = {$_.EmailAddress}}

}

# format and export

$result | Format-Table -AutoSize

# export


$result | Export-Csv -Path 'C:\Temp\table.csv' -Encoding Default  -NoTypeInformation -UseCulture

Getting a list of Cluster by Description in AD

# path to file 

$CSVpatch = "C:\temp\address.csv"

# Get

$result = Import-Csv -Path $CSVpatch -Delimiter ";" -Encoding Default | ForEach-Object {

    $server = $_.server
   
 Get-ADComputer -Identity $server -Properties * |

    Select-Object @{Name = 'server'; Expression = {$server}},

                  @{Name = 'Cluster'; Expression = {$_.Description}}

                     

}

# format and export

$result |  ft -AutoSize

# export

$result | Export-Csv -Path 'C:\Temp\table.csv' -Encoding Default  -NoTypeInformation -UseCulture

Checking the availability of the server on a specific port, ping and ip

# path to file

$CSVpatch = "C:\temp\address.csv"

# Get

$result = Import-Csv -Path $CSVpatch -Delimiter ";" -Encoding Default | ForEach-Object {

    $server = $_.server

       

 Test-NetConnection -ComputerName $server -Port 13000 |

    Select-Object @{Name = 'server'; Expression = {$server}},

                  @{Name = 'TcpTestSucceeded'; Expression = {$_.TcpTestSucceeded}},

                  @{Name = 'PingSucceeded'; Expression = {$_.PingSucceeded}},

                  @{Name = 'RemoteAddress'; Expression = {$_.RemoteAddress}}

                         

}

# format and export

$result |  ft -AutoSize

# export

$result | Export-Csv -Path 'C:\Temp\table.csv' -Encoding Default  -NoTypeInformation -UseCulture

CodePudding user response：

I'm not sure what you are trying to do here. Do you have a sample from the CSV files?
C:\temp\address.csv

That would help a lot. Based on your code you have at least these two columns:

Name <- AD User Displayname
Server <- AD Computer Name

I generally do not use Displayname as a joint or a lookup value as you can have duplicates. It's a bad practice. You should use sAMaccountName or UPN instead.

Once I see the sample I can help you.

CodePudding user response：

Using this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?), you might join multiple tables (based on a common property) at once:

$Result1 = ConvertFrom-Csv @'
server, Name,   email
Cell1,  Cell22, Cell11
Cell3,  Cell44, Cell33
'@

$Result2 = ConvertFrom-Csv @'
server, Cluster
Cell1,  Cell2
Cell3,  Cell4
'@

$Result3 = ConvertFrom-Csv @'
server, TcpTestSucceeded, PingSucceeded, RemoteAddress
Cell1,  Cell21,           Cell13,        Cell27
Cell3,  Cell41,           Cell33,        Cell47
'@

$Result1 |Join $Result2 -on Server |Join $Result3 -on Server |Format-Table

server Name   email  Cluster TcpTestSucceeded PingSucceeded RemoteAddress
------ ----   -----  ------- ---------------- ------------- -------------
Cell1  Cell22 Cell11 Cell2   Cell21           Cell13        Cell27
Cell3  Cell44 Cell33 Cell4   Cell41           Cell33        Cell47