varias unir unificar una tablas sola query power por dinamicas datos consolidar concatenar comun combinar campo scripting powershell dhcp

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 (2)

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.

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?

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