How to combine data from multiple cmdlets?

PowerShell Help
1

I have the following script to output scope data from my DHCP server. In short, I want all the scope IDs and names, and then all the configured options for those scopes. This script seems to output to the console just fine:

$scopelist = Get-DhcpServerv4Scope -ComputerName localhost | Select-Object ScopeID, Name

foreach($s in $scopelist){
    Write-Host $s
    Get-DhcpServerv4OptionValue –ScopeID $s.ScopeID | Select-Object OptionID, Name, Value
}

The output looks like this:

@{ScopeId=10.1.1.0; Name=Testscope 1}

                                     OptionID Name                                          Value                                        
                                     -------- ----                                          -----                                        
                                           51 Lease                                         {691200}                                     
                                            3 Router                                        {10.1.1.1}                                   
                                           15 DNS Domain Name                               {adatum.com}                                 
                                            6 DNS Servers                                   {192.168.1.200}                              
@{ScopeId=10.2.2.0; Name=Testscope 2}
                                           51 Lease                                         {86400}                                      
                                            3 Router                                        {10.2.2.1}                                   
                                           15 DNS Domain Name                               {foo.bar}                                    
                                            6 DNS Servers                                   {10.10.10.10}                                
                                           44 WINS/NBNS Servers                             {10.10.10.11}                                
                                           46 WINS/NBT Node Type                            {0x08}                                       
@{ScopeId=10.3.3.0; Name=Testscope 3}
                                           51 Lease                                         {691200}

The problem is, I can’t export this data to a file containing data from both cmdlets. I tried Export-CSV output.csv -Append in the foreach loop:

Write-Host $s | Export-CSV output.csv -Append
Get-DhcpServerv4OptionValue –ScopeID $s.ScopeID | Select-Object OptionID, Name, Value | Export-CSV output.csv -Append

, but then output.csv only contains the output from Get-DhcpServerv4OptionValue. I also tried “.\script.ps1 >output.csv”, it doesn’t work either. Also it would save a lot of time in Excel if I could get the data in proper table format, with ScopeID and scope name defined for all rows, like this:

ScopeID, Name,OptionID, Name, Value
10.2.2.0, Testscope 2, 3, Router, 10.2.2.1
and so on

I could not find examples how to do this, all Export-CSV help examples just take input from a single cmdlet or object. Should I make a new object first with my data? I have no idea how to pass the values to New-Object.

2

You could use a Calculated Property to combine everything into one object. Something like this:

$scopelist = Get-DhcpServerv4Scope -ComputerName localhost | Select-Object ScopeID, Name
foreach($s in $scopelist){
    Get-DhcpServerv4OptionValue –ScopeID $s.ScopeID | Select-Object @{N='ScopeID';E={$s.ScopeID}},@{N='ScopeName';E={$s.Name}},OptionID, Name, Value
}

That could then be exported to CSV very easily.

3

Thank you, that produces a great list. But:

$scopelist = Get-DhcpServerv4Scope -ComputerName localhost | Select-Object ScopeID, Name
foreach($s in $scopelist){
    Get-DhcpServerv4OptionValue –ScopeID $s.ScopeID | Select-Object @{N='ScopeID';E={$s.ScopeID}},@{N='ScopeName';E={$s.Name}},OptionID, Name, Value | Export-CSV output.csv -Append
}

gives an error: “Export-CSV : Cannot append CSV content to the following file: output.csv. The appended object does not have a property that corresponds to the following column: . To continue with mismatched properties, add the -Force parameter, and then retry the command. (+ CategoryInfo: InvalidData: (:String) [Export-Csv], InvalidOperationException + FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.PowerShell.Commands.ExportCsvCommand)”

With -Force the last Value string comes out as just “System.String”:

#TYPE Selected.Microsoft.Management.Infrastructure.CimInstance
“ScopeID”,“ScopeName”,“OptionID”,“Name”,“Value”
“10.1.1.0”,“Testscope 1”,“51”,“Lease”,“System.String”
“10.1.1.0”,“Testscope 1”,“3”,“Router”,“System.String”

Out-File instead of Export-CSV works, but it seems to be the same thing than just .\script.ps1 >output.csv, I just get a console output:

ScopeID   : 10.1.1.0
ScopeName : Testscope 1
OptionID  : 51
Name      : Lease
Value     : {691200}

ScopeID   : 10.1.1.0
ScopeName : Testscope 1
OptionID  : 3
Name      : Router
Value     : {10.1.1.1}
4

Try this:

$scopelist = Get-DhcpServerv4Scope -ComputerName localhost | Select-Object ScopeID, Name
foreach[$s in $scopelist]{
    Get-DhcpServerv4OptionValue –ScopeID $s.ScopeID | Select-Object @{N='ScopeID';E={$s.ScopeID}},@{N='ScopeName';E={$s.Name}},OptionID, Name, @{N='Value';E={$_.Value}} | Export-CSV .\Output1.csv -Append -NoTypeInformation
}

Some of the values can be arrays (eg. DNS servers), so they may not appear as you expect. Not sure what you want the output to look like in those cases.