scripting - unificar - En Powershell, ¿cuál es la mejor manera de unir dos tablas en una sola?

unir dos tablas excel por un campo comun

Question

Soy bastante nuevo en Powershell, y me pregunto si alguien sabe de alguna manera mejor de lograr el siguiente problema de ejemplo.

Tengo una matriz de asignaciones desde la dirección IP hasta el nombre del host. Esto representa una lista de arriendos DHCP activos:

PS H:/> $leases

IP                    Name
--                    ----
192.168.1.1           Apple
192.168.1.2           Pear
192.168.1.3           Banana
192.168.1.99          FishyPC

Tengo otra matriz de asignaciones desde la dirección MAC a la dirección IP. Esto representa una lista de reservas de IP:

PS H:/> $reservations

IP                    MAC
--                    ---
192.168.1.1           001D606839C2
192.168.1.2           00E018782BE1
192.168.1.3           0022192AF09C
192.168.1.4           0013D4352A0D

Para mayor comodidad, pude producir una tercera matriz de asignaciones desde la dirección MAC a la dirección IP y nombre de host utilizando el siguiente código. La idea es que $reservations obtenga un tercer campo, "Nombre", que se rellena siempre que haya un campo "IP" coincidente:

$reservations = $reservations | foreach {
    $res = $_
    $match = $leases | where {$_.IP -eq $res.IP} | select -unique
    if ($match -ne $NULL) {
        "" | select @{n="IP";e={$res.IP}}, @{n="MAC";e={$res.MAC}}, @{n="Name";e={$match.Name}}
    }
}

El resultado deseado es algo como esto:

PS H:/> $ideal

IP                    MAC                 Name
--                    ---                 ----
192.168.1.1           001D606839C2        Apple
192.168.1.2           00E018782BE1        Pear
192.168.1.3           0022192AF09C        Banana
192.168.1.4           0013D4352A0D

¿Hay alguna forma mejor de hacer esto?

Answer 1

Lee Holmes escribió una publicación de blog sobre una función Join-Object que hace lo que quiere. Lástima que aún no está integrado en PowerShell.

Answer 2

Join.ps1

La función Join-Object (alias Join ) combina columnas de dos matrices de objetos en una nueva matriz de objetos que puede guardarse como una tabla ( Export-CSV ) o utilizarse tal cual.

Function Join-Object {
    [CmdletBinding()]Param (
        [PSObject[]]$RightTable, [Alias("Using")]$On, $Merge = @{}, [Parameter(ValueFromPipeLine = $True)][Object[]]$LeftTable, [String]$Equals
    )
    $Type = ($MyInvocation.InvocationName -Split "-")[0]
    $PipeLine = $Input | ForEach {$_}; If ($PipeLine) {$LeftTable = $PipeLine}
    If ($LeftTable -eq $Null) {If ($RightTable[0] -is [Array]) {$LeftTable = $RightTable[0]; $RightTable = $RightTable[-1]} Else {$LeftTable = $RightTable}}
    $DefaultMerge = If ($Merge -is [ScriptBlock]) {$Merge; $Merge = @{}} ElseIf ($Merge."") {$Merge.""} Else {{$Left.$_, $Right.$_}}
    If ($Equals) {$Merge.$Equals = {If ($Left.$Equals -ne $Null) {$Left.$Equals} Else {$Right.$Equals}}}
    ElseIf ($On -is [String] -or $On -is [Array]) {@($On) | ForEach {If (!$Merge.$_) {$Merge.$_ = {$Left.$_}}}}
    $LeftKeys  = @($LeftTable[0].PSObject.Properties  | ForEach {$_.Name})
    $RightKeys = @($RightTable[0].PSObject.Properties | ForEach {$_.Name})
    $Keys = $LeftKeys + $RightKeys | Select -Unique
    $Keys | Where {!$Merge.$_} | ForEach {$Merge.$_ = $DefaultMerge}
    $Properties = @{}; $Keys | ForEach {$Properties.$_ = $Null}; $Out = New-Object PSObject -Property $Properties
    $LeftOut  = @($True) * @($LeftTable).Length; $RightOut = @($True) * @($RightTable).Length
    $NullObject = New-Object PSObject
    For ($LeftIndex = 0; $LeftIndex -lt $LeftOut.Length; $LeftIndex++) {$Left = $LeftTable[$LeftIndex]
        For ($RightIndex = 0; $RightIndex -lt $RightOut.Length; $RightIndex++) {$Right = $RightTable[$RightIndex]
            $Select = If ($On -is [String]) {If ($Equals) {$Left.$On -eq $Right.$Equals} Else {$Left.$On -eq $Right.$On}}
            ElseIf ($On -is [Array]) {($On | Where {!($Left.$_ -eq $Right.$_)}) -eq $Null} ElseIf ($On -is [ScriptBlock]) {&$On} Else {$True}
            If ($Select) {$Keys | ForEach {$Out.$_ = 
                    If ($LeftKeys -NotContains $_) {$Right.$_} ElseIf ($RightKeys -NotContains $_) {$Left.$_} Else {&$Merge.$_}
                }; $Out; $LeftOut[$LeftIndex], $RightOut[$RightIndex] = $Null
    }   }   }
    If ("LeftJoin",  "FullJoin" -Contains $Type) {
        For ($LeftIndex = 0; $LeftIndex -lt $LeftOut.Length; $LeftIndex++) {
            If ($LeftOut[$LeftIndex]) {$Keys | ForEach {$Out.$_ = $LeftTable[$LeftIndex].$_}; $Out}
    }   }
    If ("RightJoin", "FullJoin" -Contains $Type) {
        For ($RightIndex = 0; $RightIndex -lt $RightOut.Length; $RightIndex++) {
            If ($RightOut[$RightIndex]) {$Keys | ForEach {$Out.$_ = $RightTable[$RightIndex].$_}; $Out}
    }   }
}; Set-Alias Join   Join-Object
Set-Alias InnerJoin Join-Object; Set-Alias InnerJoin-Object Join-Object -Description "Returns records that have matching values in both tables"
Set-Alias LeftJoin  Join-Object; Set-Alias LeftJoin-Object  Join-Object -Description "Returns all records from the left table and the matched records from the right table"
Set-Alias RightJoin Join-Object; Set-Alias RightJoin-Object Join-Object -Description "Returns all records from the right table and the matched records from the left table"
Set-Alias FullJoin  Join-Object; Set-Alias FullJoin-Object  Join-Object -Description "Returns all records when there is a match in either left or right table"

Sintaxis

<Object[]> | InnerJoin|LeftJoin|RightJoin|FullJoin <Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]

InnerJoin|LeftJoin|RightJoin|FullJoin <Object[]>,<Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]

InnerJoin|LeftJoin|RightJoin|FullJoin -LeftTable <Object[]> -RightTable <Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]

Comandos

La función Join-Object (alias Join ) es una función con varios alias que une dos tablas (cada una compuesta por una matriz de PSCustomObjects ) similar a las respectivas instrucciones de SQL Join . El tipo de combinación predeterminado es un InnerJoin .

• InnerJoin-Object (alias InnerJoin )
  Devuelve registros que tienen valores coincidentes en ambas tablas.

• LeftJoin-Object (alias LeftJoin )
  Devuelve todos los registros de la tabla izquierda y los registros coincidentes de la tabla correcta.

• RightJoin-Object (alias RightJoin )
  Devuelve todos los registros de la tabla correcta y los registros coincidentes de la tabla correcta.

• FullJoin-Object (alias FullJoin )
  Devuelve todos los registros cuando hay una coincidencia en la tabla izquierda o derecha.

Notas

1. Todos los comandos de unión son compatibles con PowerShell versión 2 y superior.

Parámetros

-LeftTable <Object[]> y -RightTable <Object[]>

Los parámetros -LeftTable y RightTable definen la tabla izquierda y derecha a unir. Hay tres posibles sintaxis para suministrar las tablas:

• Uso de la canalización de PowerShell: <LeftTable> | Join <RightTable> <LeftTable> | Join <RightTable>

• Suministrando ambas tablas en una matriz (separadas por una coma) en la primera posición del argumento: Join <LeftTable> , <RightTable>

• Suministro de ambas tablas con argumentos con nombre: Join -Left <LeftTable> -Right <RightTable>

Notas

1. Si solo se proporciona una tabla ( Join <Table> ), se realizará una auto-unión en la tabla.

-On <String>|<Array>|<ScriptBlock> y -Equals <String>

El parámetro -On (alias Using ) define la condición que especifica cómo unir tablas y qué filas incluir en el conjunto de resultados (interno). El parámetro -On admite los siguientes formatos:

• String -Equals <String> Si el valor -On es un String y se suministran los parámetros -Equals <String> , la propiedad en la columna izquierda definida por el valor -On debe ser igual a la propiedad en la columna derecha definida por el valor de -equals que se incluirá en el conjunto de resultados (interno).

• String o Array Si el valor es String o Array el parámetro -On es similar al SQL que using cláusula. Esto significa que todas las propiedades enumeradas deben ser iguales (en el lado izquierdo y derecho) para ser incluidas en el conjunto de resultados (interno). Las propiedades enumeradas generarán un único valor por defecto (ver también -Expressions ).

• ScriptBlock Cualquier expresión condicional donde $Left define la fila de la izquierda, $Right define la fila de la derecha.

Notas

1. El tipo ScriptBlock tiene la mayor cantidad de posibilidades de comparación, pero es considerablemente más lento que los otros tipos.

2. Si se omite el parámetro -On o de un tipo desconocido, se realizará una unión cruzada .

-Merge <HashTable>|<ScriptBlock>

Define cómo deben fusionarse las columnas específicas con el mismo nombre. El parámetro -Merge acepta tipos: una HashTable contiene la expresión de fusión específica para cada columna o ScriptBlock contiene la expresión de fusión predeterminada para todas las columnas que no tienen definida la expresión de fusión.
Donde en la expresión:

• $_ contiene el nombre de cada columna.
• $Left tiene la fila de la izquierda y $Right tiene la fila de la derecha.
• $Left.$_ Contiene cada valor a la izquierda y $Right.$_ Contiene cada valor correcto.
• $LeftIndex contiene el índice actual de la fila izquierda y $RightIndex contiene el índice actual de la fila derecha.

Notas:

1. Las expresiones solo se ejecutan si el valor de la izquierda ( Left.$_ ) Y el valor de la derecha ( Left.$_ ) Están presentes (incluidos los valores que son $Null ), de lo contrario, solo se devuelve el valor de salida.

2. Si no se define una expresión para una columna, se utiliza la expresión {$Left.$_, $Right.$_} . Esto significa que ambos valores están asignados (en una matriz) a la propiedad actual.

3. La expresión para columnas definidas por -On <String> , -Equals <String> y -On <Array> es: {$Left.$_} Y solo puede ser anulada por una expresión específica de columna definida en una tabla hash. Esto significa que un valor único ( $Left o $Right que no es igual a $Null ) se asigna a la propiedad actual.

4. Para usar expresiones específicas de columna y definir una expresión predeterminada, use un nombre de clave de longitud cero para la expresión predeterminada, por ejemplo -Merge @{"" = {$Left.$_}; "Column Name" = {$Right.$_}} -Merge @{"" = {$Left.$_}; "Column Name" = {$Right.$_}}

Ejemplos

Dadas las siguientes tablas:

   $Employee                               $Department
+---------+---------+-------------+    +-------------+---------+---------+
|  Name   | Country | Department  |    |    Name     | Country | Manager |
+---------+---------+-------------+    +-------------+---------+---------+
| Aerts   | Belgium | Sales       |    | Engineering | Germany | Meyer   |
| Bauer   | Germany | Engineering |    | Marketing   | England | Morris  |
| Cook    | England | Sales       |    | Sales       | France  | Millet  |
| Duval   | France  | Engineering |    +-------------+---------+---------+
| Evans   | England | Marketing   |
| Fischer | Germany | Engineering |
+---------+---------+-------------+

PS C:/> # InnerJoin on Department = Name
PS C:/> $Employee | InnerJoin $Department Department -eq Name | Format-Table

Department  Name    Manager Country
----------  ----    ------- -------
Sales       Aerts   Millet  {Belgium, France}
Engineering Bauer   Meyer   {Germany, Germany}
Sales       Cook    Millet  {England, France}
Engineering Duval   Meyer   {France, Germany}
Marketing   Evans   Morris  {England, England}
Engineering Fischer Meyer   {Germany, Germany}


PS C:/> # LeftJoin using country (selecting Department.Name and Department.Country)
PS C:/> $Employee | LeftJoin ($Department | Select Manager,Country) Country | Format-Table

Department  Name    Manager Country
----------  ----    ------- -------
Engineering Bauer   Meyer   Germany
Sales       Cook    Morris  England
Engineering Duval   Millet  France
Marketing   Evans   Morris  England
Engineering Fischer Meyer   Germany
Sales       Aerts           Belgium


PS C:/> # InnerJoin on Employee.Department = Department.Name and Employee.Country = Department.Country (returning only the left name and - country)
PS C:/> $Employee | InnerJoin $Department {$Left.Department -eq $Right.Name -and $Left.Country -eq $Right.Country} {$Left.$_}

Department  Name    Manager Country
----------  ----    ------- -------
Engineering Bauer   Meyer   Germany
Marketing   Evans   Morris  England
Engineering Fischer Meyer   Germany


PS C:/> # Cross Join
PS C:/> $Employee | InnerJoin $Department | Format-Table

Department  Name                   Manager Country
----------  ----                   ------- -------
Sales       {Aerts, Engineering}   Meyer   {Belgium, Germany}
Sales       {Aerts, Marketing}     Morris  {Belgium, England}
Sales       {Aerts, Sales}         Millet  {Belgium, France}
Engineering {Bauer, Engineering}   Meyer   {Germany, Germany}
Engineering {Bauer, Marketing}     Morris  {Germany, England}
Engineering {Bauer, Sales}         Millet  {Germany, France}
Sales       {Cook, Engineering}    Meyer   {England, Germany}
Sales       {Cook, Marketing}      Morris  {England, England}
Sales       {Cook, Sales}          Millet  {England, France}
Engineering {Duval, Engineering}   Meyer   {France, Germany}
Engineering {Duval, Marketing}     Morris  {France, England}
Engineering {Duval, Sales}         Millet  {France, France}
Marketing   {Evans, Engineering}   Meyer   {England, Germany}
Marketing   {Evans, Marketing}     Morris  {England, England}
Marketing   {Evans, Sales}         Millet  {England, France}
Engineering {Fischer, Engineering} Meyer   {Germany, Germany}
Engineering {Fischer, Marketing}   Morris  {Germany, England}
Engineering {Fischer, Sales}       Millet  {Germany, France}

Actualizar la lista de servicios (reemplazar los servicios existentes por el nombre y agregar nuevos)

Import-CSV ./Svc.csv | LeftJoin (Get-Service) Name {$Right.$_} | Export-CSV ./Svc.csv

Actualizar la lista de procesos y solo insertar procesos con una CPU más alta

Import-CSV ./CPU.csv | LeftJoin (Get-Process) ID {If ($Left.CPU -gt $Right.CPU) {$Left.$_} Else {$Right.$_}} | Export-CSV ./CPU.csv