6 métodos para poder escribir la salida de PowerShell en una tabla de SQL Server

PowerShell se ha transformado en la mejor opción para muchos de los administradores de bases de datos y esto se debe a su forma tan eficiente de manejar y administrar la automatización de una manera simple y rápida. Está construido sobre .NET Framework y usa modelos de objetos como COM, ADSI, ADO y WMI. PowerShell ha reemplazado la forma tradicional de secuencias de comandos que usaba secuencias de comandos heredadas para poder monitorear instancias de SQL.

En muchas ocasiones me han hecho la pregunta de cómo almacenar la salida de los datos de PowerShell WMI en la tabla SQL. Esta pregunta surge con tanta frecuencia que decidí escribir este artículo.

El momento en el cual se envían datos dentro de un sistema (como un objeto de PowerShell a un cmdlet), el proceso es sencillo. Sin embargo, con el intercambio de datos no nativos (por ejemplo, WMI a SQL), el proceso puede complicarse de una forma potencial. Debido a esto, hay muchos puristas que sugieren apegarse a formatos de intercambio simples, como CSV, JSON o, en algunos casos, XML.

Veamos las posibles opciones para poder transformar los datos de WMI a la tabla SQL. En este artículo, vamos a hacer lo siguiente:

Hablar sobre Invoke-Sqlcmd
Hablar sobre las bibliotecas de clases .NET
Hablar sobre la exportación de datos utilizando varios cmdlets Export *
Poder aprender a usar el Instrumental de administración de Windows (WMI)
Discutir sobre las sentencias SQL para cargar datos del archivo
Y más

Esta guía detalla el ejemplo de trabajo de verificar el espacio en disco consultando WMI.

Microsoft incorporó WMI para así poder simplificar la administración de los diferentes tipos de sistemas operativos. Si bien la automatización fue una de las razones para traer WMI, el objetivo principal era el poder proporcionar a los desarrolladores con identificadores que pudieran utilizar al poder crear aplicaciones para Windows. Pero, por supuesto, WMI también tiene estos ingeniosos usos.

La forma más fácil de conseguir la información de espacio en disco es con la clase Win32_LogicalDisk. Filtramos el contenido para así poder elegir solo el DriveType = 3, que es el número de tipo para unidades locales.

1 2	Get-WmiObject win32_logicaldisk -ComputerName <ComputerName> -Filter "Drivetype=3" \|` select SystemName,DeviceID,VolumeName,@{Label="Total Size";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }}\|Format-Table -AutoSize

Analicemos la transformación de los datos anteriores en una tabla SQL utilizando algunos de los métodos directos e indirectos en esta publicación:

Utilizando Invoke-Sqlcmd
Utilizando ADO
Consulta WMI
Utilizando comandos de exportación como JSON, XML y CSV

Prerrequisitos

Antes de seguir adelante, vamos a ver lo que necesitamos antes de poder continuar:

SSMS 17.2
Modulo de SQL Server PowerShell

Empezando

Vamos a comenzar ahora a transformar los datos que recibimos en una tabla SQL.

Invoke-Sqlcmd

Invoke-Sqlcmd es un tipo de contenedor y una versión de PowerShell del comando sqlcmd de SQL Server con capacidades adicionales, como por ejemplo la manipulación de datos y las transformaciones de datos con un enfoque en los datos de salida.

El proceso es bastante simple:

Crear tabla PoShDisk Table

CREATE TABLE tbl_PoShDisk

(

[SystemName] VARCHAR(40) not null,

[DeviceID] VARCHAR(40) not null,

[VolumeName] VARCHAR(40) not null,

[TotalSize] int not null,

[FreeSize] int not null

)

Importar el módulo SqlServer
Declarar la variable y la cadena de conexión
Defina la función que prepara los datos WMI para la inserción de SQL
Consulta la clase Win32_LogicalDisk de WMI
Recorrer el conjunto de resultados
Inserte los datos en la tabla SQL

Empecemos a preparar el script:

#Declare Servername

$sqlServer='hqdbsp18'

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#Fucntion to manipulate the data

Function writeDiskInfo

{

param($server,$devId,$volName,$frSpace,$totSpace)

$totSpace=[math]::Round(($totSpace/1073741824),2)

$frSpace=[Math]::Round(($frSpace/1073741824),2)

$usedSpace = $totSpace - $frspace

$usedSpace=[Math]::Round($usedSpace,2)

# Data preparation for loading data into SQL table

$InsertResults = @"

INSERT INTO [SQLShackDemo].[dbo].[tbl_PosHdisk](SystemName,DeviceID,VolumeName,TotalSize,FreeSize)

VALUES ('$SERVER','$devId','$volName',$totSpace,$usedSpace)

#call the invoke-sqlcmdlet to execute the query

Invoke-sqlcmd @params -Query $InsertResults

}

#Query WMI query to store the result in a varaible

$dp = Get-WmiObject win32_logicaldisk -ComputerName $sqlServer| Where-Object {$_.drivetype -eq 3}

#Loop through array

foreach ($item in $dp)

{

#Call the function to transform the data and prepare the data for insertion

writeDiskInfo $sqlServer $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

}

#Query the destination table to view the result

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

La captura de pantalla a continuación nos muestra los pasos y los resultados. La salida se consulta desde la tabla tbl_PoShDisk

Ahora continuemos. Podemos ver cómo se transfirieron los datos de la consulta WMI a una tabla SQL

Objetos ADO.NET

Otro método para que podamos ejecutar una consulta en PowerShell es el poder utilizar las bibliotecas ADO.NET, las cuales requieren crear un DataSet, luego crear un DataAdapter y finalmente llenar el DataAdapter. Para muchas necesidades de recuperación de datos desde scripts, ADO.NET puede llegar a ser demasiado pesado. Afortunadamente, hay varias maneras de simplificar esta tarea y conservar los beneficios de .NET DataTables. Vamos a ver tres métodos para lograr obtener los datos de SQL Server de PowerShell. Una vez que usted tenga los datos en DataTable, podríamos lograr transformar los datos en varias cosas, incluida la canalización de la salida a uno de los cmdlets integrados.

ADO.NET es un conjunto de clases de bibliotecas que forman parte del .NET Framework. Las clases ADO.NET por lo general se dividen en dos tipos: clases conectadas y clases desconectadas.

Clase Conectada

SqlConnection – este se conecta al proveedor de datos SQL Server .NET para poder establecer y administrar la conexión a la base de datos de destino
SqlCommand – este contiene los detalles necesarios para lograr emitir un comando T-SQL contra una base de datos de SQL Server
SqlDataAdapter – nos proporciona un puente entre las clases conectadas y las clases desconectadas. Esta clase incluye los métodos de relleno y actualización. Puede utilizar el método de relleno para completar un objeto DataSet o DataTable. Puede usar el método Actualizar para propagar datos actualizados en un objeto DataSet o DataTable a la base de datos
SqlBulkCopy – carga de forma más eficiente una tabla de SQL Server con datos de otra fuente

Clases Desconectadas

DataTable – seste almacena los datos devueltos por su consulta. Los datos se almacenan en filas y columnas, de una forma similar a cómo se almacenan los datos en una tabla de base de datos

Preparación de los scripts

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#Server to query WMI class win32_logicalDisks

$server = 'hqdbsp18'

#Prepare Insert Statement

$insert = @'

INSERT INTO [SQLShackDemo].[dbo].[tbl_PosHdisk](SystemName,DeviceID,VolumeName,TotalSize,FreeSize)

VALUES ('{0}','{1}','{2}','{3}','{4}')

Try {

#Define connction string of target database

$connectionString = 'Data Source=HQDBT01;Initial Catalog=SQLShackDemo;Integrated Security=SSPI'

# connection object initialization

$conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)

#Open the Connection

$conn.Open()

# Prepare the SQL

$cmd = $conn.CreateCommand()

#WMI ouput transformation to SQL table

Get-WmiObject win32_logicaldisk -ComputerName $server -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="TotalSize";Expression={$_.Size / 1gb -as [int] }},@{Label="FreeSize";Expression={$_.freespace / 1gb -as [int] }}|`

ForEach-Object{

$cmd.CommandText = $insert -f $_.SystemName, $_.DeviceID, $_.VolumeName, $_.TotalSize, $_.FreeSize

$cmd.ExecuteNonQuery()

}

#Close the connection

$conn.Close()

}

Catch {

Throw $_

}

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

La salida se da a continuación:

Usando SqlBulkCopy

Vamos a escribir una función para poder realizar la operación de copia. La función, Out-DataTable se puede hallar en el Apéndice (A). Esta función se encarga de convertir la salida de la consulta WMI a la tabla de datos. La salida de la tabla de datos se alimenta de la clase SqlBulkCopy para así poder escribir los datos en la tabla SQL. La clase SqlBulkCopy carga una tabla de SQL Server con los datos de otra fuente que en este caso es Win32_LogicalDisks.

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#function to retrieve disk information

Function Get-DisksSpace ([string]$Servername)

{

Get-WmiObject win32_logicaldisk -ComputerName $Servername -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }}

}

#Variable to hold output as data-table

$dataTable = Get-DisksSpace hqdbsp18 | Out-DataTable

#Define Connection string

$connectionString = "Data Source=hqdbt01; Integrated Security=True;Initial Catalog=SQLShackDemo;"

#Bulk copy object instantiation

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

#Define the destination table

$bulkCopy.DestinationTableName = "tbl_PosHdisk"

#load the data into the target

$bulkCopy.WriteToServer($dataTable)

#Query the target table to see for output

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

Use las opciones de exportación de PowerShell con construcciones SQL

A su vez también podemos utilizar construcciones SQL con las opciones de exportación de PowerShell. Nosotros podemos:

Exportar los datos WMI a un archivo JSON y poder consultarlo utilizando construcciones JSON nativas de SQL
Exportar a datos XML y poder consultar usando Nodes ()
Exportar los datos a formato CSV y utilizar el comando SQL Bulk-Insert nativo para poder consultar los datos

Vamos a ver estos procesos uno por uno.

Usando JSON

El cmdlet ConvertTo-Json va a convertir un objeto en una cadena JSON válida. Con este cmdlet, la salida se convierte a JSON (notación de objetos JavaScript). Luego se va a consultar el archivo JSON usando la construcción JSON en SQL Server, la cual se llama OPEN JSON, para así transformar los datos de la clase win32_LogicalDisk WMI, en una tabla relacional.

La salida WMI se va almacenar en una variable que luego se escribe en un archivo JSON utilizando el cmdlet de formato Out-File.

$JSON=Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |ConvertTo-Json

$json |Out-File \\hqdbt01\f$\PowerSQL\DiskSpace.JSON

La salida de este archivo JSON se muestra a continuación:

Ahora vamos a almacenar el JSON en una tabla SQL:

SELECT t.*

FROM

OPENROWSET(BULK N'\\hqdbt01\f$\PowerSQL\DiskSpace.JSON', SINGLE_NCLOB) AS JSON

CROSS APPLY OPENJSON(BulkColumn)

WITH(

Server NVARCHAR(10),

DeviceID NVARCHAR(20),

VolumeName NVARCHAR(20),

[Total SIze] DECIMAL(5,2),

[Free Size] DECIMAL(5,2)

) AS t

Los datos pueden ser almacenados a la tabla usando la instrucción Insert SQL:

Usando XML

Los datos XML vienen del archivo XML y se van a almacenar en una columna de columna; utilice los métodos XML y, extraiga valores con xml.value (), proyecte nodos con xml.nodes (), use CROSS APPLY para tratar de unir sus nodos para llegar a derivar todos los valores de los nodos.

La consulta que se muestra a continuación escribe la salida WMI en un archivo XML:

Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |ConvertTo-Xml -as String -NoTypeInformation|`

Set-Content -path \\hqdbt01\f$\PowerSQL\DiskSpace.xml

El siguiente es el archivo XML generado:

Cargar los datos en la tabla temporal la cual contiene XMLData como su columna
Utilice la cláusula OPENROWSET para lograr cargar datos XML en una sola columna
Transforme cada uno de los nodos utilizando el método XML value() y Query() para derivar sus valores

CREATE TABLE tbl_XMLDisk

(

XMLData XML,

)

--truncate table XMLwithOpenXML

INSERT INTO tbl_XMLDisk(XMLData)

SELECT

CONVERT(XML, BulkColumn) AS BulkColumn

FROM

OPENROWSET(BULK 'f:\PowerSQL\DiskSpace.xml', SINGLE_BLOB) AS x;

SELECT XMLData FROM tbl_XMLDisk

SELECT

p.value('(./Property)[1]', 'VARCHAR(20)') AS SystemName,

p.value('(./Property)[2]', 'VARCHAR(30)') AS DeviceID,

p.value('(./Property)[3]', 'VARCHAR(30)') AS VolumeName,

p.value('(./Property)[4]', 'int') AS [Total SIze],

p.value('(./Property)[5]', 'int') AS [Free Size]

FROM tbl_XMLDisk

CROSS APPLY XMLData.nodes('/Objects/Object') t(p)

La salida de la consulta XML

Usando BULK INSERT

La primera declaración que vamos a ver es el BULK INSERT, el cual nos permite importar datos de un archivo de datos a una tabla. En el siguiente ejemplo, se importó los datos del archivo CSV a la tabla SQL:

Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |`

ConvertTo-Csv -NoTypeInformation| Set-Content -path \\hqdbt01\f$\PowerSQL\DiskSpace.csv

--Create table.

CREATE TABLE tbl_CSVDisk

(

[SystemName] VARCHAR(40),

[DeviceID] VARCHAR(40),

[VolumeName] VARCHAR(40),

[TotalSize] VARCHAR(40),

[FreeSize] VARCHAR(40)

)

--Load the data into the SQL table starting with 2 row, comma(‘,’) as delimiter and newline as

--rowseparator

BULK

INSERT tbl_CSVDisk

FROM 'F:\PowerSQL\DiskSpace.csv'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

--Check the content of the table.

SELECT REPLACE(systemName,'"','') systemName,

REPLACE([DeviceID],'"','') [DeviceID],

REPLACE([VolumeName],'"','') [VolumeName],

REPLACE([TotalSize],'"','') [TotalSize],

REPLACE([FreeSize],'"','') [FreeSize]

FROM tbl_CSVDisk

Conclusiones

Se pueden usar diferentes métodos y diversas técnicas para poder lograr un resultado específico. El desafío es el tratar de utilizar la herramienta correcta, de la manera correcta, para el trabajo correcto: el proverbial problema de “clavar un tornillo con un martillo”. Cuando se nos presenta un nuevo conjunto de herramientas, no debemos intentar utilizar de la misma manera que otras herramientas que hemos usado en el pasado. En vez de eso, debemos aprender a utilizar la herramienta para que podamos aprovecharla al máximo en su proceso.

En este caso, es muy probable que tengamos que considerar la eficiencia de los métodos que enumeramos y tomar una decisión sobre qué método debemos usar.

Apéndice (A)

Function Out-DataTable

{

$dt = new-object Data.datatable

$First = $true

foreach ($item in $input){

$DR = $DT.NewRow()

$Item.PsObject.get_properties() | foreach {

if ($first) {

$Col = new-object Data.DataColumn

$Col.ColumnName = $_.Name.ToString()

$DT.Columns.Add($Col) }

if ($_.value -eq $null) {

$DR.Item($_.Name) = "[empty]"

}

elseif ($_.IsArray) {

$DR.Item($_.Name) =[string]::Join($_.value ,";")

}

else {

$DR.Item($_.Name) = $_.value

}

$DT.Rows.Add($DR)

$First = $false

}

return @(,($dt))

}

Acerca de
Últimas entradas

Prashanth Jayaram

Soy un experto en tecnologías con más de 11 años de experiencia en tecnologías de base de datos. Soy Microsoft Certified Professional y tengo el respaldo de una Licenciatura en Master en aplicaciones de computadoras.

Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.

Ver todas las publicaciones de Prashanth Jayaram

Últimas entradas de Prashanth Jayaram (ver todo)

Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
Revisión, ejemplos y uso de SQL Union - November 4, 2019

Prerrequisitos

Empezando

Invoke-Sqlcmd

Objetos ADO.NET

Usando SqlBulkCopy

Use las opciones de exportación de PowerShell con construcciones SQL

Usando JSON

Usando XML

Usando BULK INSERT

Conclusiones

Apéndice (A)

Related posts: