How to combine data from multiple cmdlets?

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Sean Quinlan Sean Quinlan 1 year, 11 months ago.

  • Author
    Posts
  • #21204
    Profile photo of -mat-
    -mat-
    Participant

    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.

  • #21225
    Profile photo of Sean Quinlan
    Sean Quinlan
    Participant

    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.

  • #21252
    Profile photo of -mat-
    -mat-
    Participant

    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}
    
  • #21313
    Profile photo of Sean Quinlan
    Sean Quinlan
    Participant

    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.

You must be logged in to reply to this topic.