Prashanth Jayaram

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

October 14, 2019 by

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.


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:

  1. Utilizando Invoke-Sqlcmd
  2. Utilizando ADO
  3. Consulta WMI
  4. 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:

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:

  1. Crear tabla PoShDisk Table
  2. Importar el módulo SqlServer
  3. Declarar la variable y la cadena de conexión
  4. Defina la función que prepara los datos WMI para la inserción de SQL
  5. Consulta la clase Win32_LogicalDisk de WMI
  6. Recorrer el conjunto de resultados
  7. Inserte los datos en la tabla SQL

Empecemos a preparar el script:

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

  1. 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
  2. SqlCommand – este contiene los detalles necesarios para lograr emitir un comando T-SQL contra una base de datos de SQL Server
  3. 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
  4. SqlBulkCopy carga de forma más eficiente una tabla de SQL Server con datos de otra fuente

Clases Desconectadas

  1. 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

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.

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.

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

Ahora vamos a almacenar el JSON en una tabla SQL:

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:

El siguiente es el archivo XML generado:

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

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:




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)

Prashanth Jayaram

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
Prashanth Jayaram
Bulk insert, PowerShell

Acerca de 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

115 Views