Gráfico evolutivo del espacio en unidades de disco (II)

En este segundo artículo vamos a ver cómo presentar la información recogida en el artículo anterior. Vamos a hacer uso de Reporting Services de SQL Server, para crear de forma sencilla gráficas que representen la información almacenada y publicarlas en un entorno web.
A modo de recordatorio, en el primer capítulo creamos un script en powershell que generaba un fichero .txt en el que cada línea representaba una unidad de disco de un servidor, el espacio total de esta unidad, el utilizado y el libre. Este script era utilizado por una tarea programada, de forma programábamos su ejecución diaria. Por otra parte, creamos una base de datos con cuatro tablas y un procedimiento almacenado. El procedimiento almacenado también se ejecutaba a diario como un trabajo en el agente de SQL Server (aunque después de la ejecución script en powershell) de forma que utilizaba la información de contenida en el fichero txt de resultados y realizaba su inserción en la base de datos.
Una vez que disponemos de la información en nuestra BB.DD., el siguiente paso va a ser mostrarla. Como hemos dicho, emplearemos Reporting Services:

De tal manera que podremos, de forma sencilla, mostrar la información mediante gráficas en entorno web:

En primer lugar deberemos crear nuestro proyecto, para lo que utilizaremos la herramienta SQL Server Business Intelligence Development Studio (que no es una opción "marcada" por defecto en la instalación de SQL Server, por lo que deberemos comprobar si la tenemos instalada, o realizar su instalación).
Al abrir el SQL Server BI Development Studio, crearemos un nuevo proyecto de Report Server: menú "Archivo" à "Nuevo proyecto" y seleccionamos como tipo de proyecto "Business Intelligence Projects" y la plantilla "Report Server Project":

En el Explorador de Soluciones podremos agregar un nuevo elemento a nuestro proyecto

Que va a ser un "report"

El report que vamos a crear va a disponer de:

• dos textbox en los que introducir la fecha de la consulta (fecha de inicio/fecha final),
• dos listas desplegables (dropdownlist) enlazadas, una con la lista de servidores y otra con las unidades de disco que tiene cada uno,
• una gráfica, y
• una tabla dinámica que muestre los datos (espacio por fecha en el servidor y unidad de disco consultada).

Nuestro primer paso será crear las consultas SQL que emplearemos en nuestro proyecto.
La primera de las listas desplegables mostrará un listado de los servidores, cuyos datos como vimos en el primer artículo, se encuentran en la tabla TServidores. La consulta SQL será muy simple:
SELECT IdServidor, NServidor FROM TServidores

Para añadir esta consulta, debemos ir a la pestaña "Datos" y crearnos un nuevo "conjunto de datos", que en mi caso he denominado "Servidores":

La segunda lista desplegable mostrará, dependiendo del valor elegido en la lista de servidores, las unidades de disco "asociadas" a ese servidor. La consulta SQL hará un cruce entre las tablas de unidades de discos y las de servidores:

SELECT TServidores.NServidor, IdServer, NUnidad FROM TDiscosServ INNER JOIN TServidores ON TServidores.IdServidor=TDiscosServ.IdServer WHERE (IdServer = @IdentServ)

Este conjunto de datos lo he denominado "DiscosServ", y como vemos hacemos uso de la variable "IdentServ" que es un parámetro del informe, como veremos más adelante, que es simplemente el valor seleccionado en la otra lista desplegable:

Por último, deberemos definir la consulta SQL "principal", es decir, aquella que nos va a mostrar la evolución de la utilización del espacio en disco. Esta es:

SELECT NServ, NUnidad, TotalEspacio, EspacioUsado, EspacioLibre, FechReg FROM RegSpace WHERE (NServ = @NombreServidor) AND (NUnidad = @NombreDisco) AND (FechReg BETWEEN @FechaIni AND @FechaFin)

En esta consulta hacemos uso de cuatro variables, que definiremos como parámetros. Esta consulta es otro conjunto de datos, que he denominado "ServerFreeSpace":

Con esto ya hemos completado todos los elementos que vamos a utilizar en la pestaña "Datos" de nuestro proyecto. En el siguiente paso vamos a trabajar con la pestaña "Diseño", y vamos a insertar el gráfico y la tabla dinámica, además de crear los parámetros necesarios para pasar valores a las consultas que hemos visto anteriormente y las propias listas desplegables y los cuadros de texto para recoger las fechas.
Para crear un parámetro, pulsaremos con el botón secundario del ratón en el margen del informe como vemos en la siguiente captura:

Y al pulsar en "Parámetros del informe" nos aparecerá una ventana en la que podremos ir creando los parámetros que necesitemos. Hemos visto que necesitamos cinco para las consultas SQL: IdentServ, NombreServidor, NombreDisco, FechaIni, FechaFin. Los tres primeros serán parámetros "internos", es decir, simplemente para pasar datos:

Y los dos parámetros de fecha serán de tipo DateTime, de forma que automáticamente Reporting Services nos genera un textbox con un icono de calendario en el lado derecho de dicho cuadro:

Añadimos valores predeterminados, de forma que si el usuario no introduce fecha alguna, realizará una consulta desde el primer día en el que comenzamos a almacenar datos hasta la fecha actual.

Además vamos a definir otros dos parámetros que nos generarán las listas desplegables. Para ello utilizaremos como "Tipo de datos" un string, y especificaremos que los "valores disponibles" sean de consulta, especificando los conjuntos de datos creados con anterioridad:

Una vez que hemos definido nuestros parámetros y nuestros elementos para especificar la consulta, procedemos a insertar el gráfico:

Y la tabla dinámica:

Y por último, para dejar más bonito nuestro informe, podemos añadir en la cabecera del informe un cuadro de texto que muestre el nombre del servidor del que estamos mostrando los datos:

Este es un sencillo ejemplo en el que hemos combinado la utilización de Powershell y el uso de una herramienta como Reporting Services para obtener una aplicación web que nos permita ir conociendo la evolución del uso de los discos en nuestro servidores, ya sea para descubrir visualmente posibles descensos bruscos en la capacidad de los mismos, o para prever cuándo podemos empezar a tener problemas de espacio y buscar soluciones.

Gráfico evolutivo del espacio en unidades en disco (I)

En los siguientes artículos voy a tratar cómo crear un sencillo sistema que permita almacenar en una BB.DD. la evolución del espacio en disco en los distintos servidores existentes en nuestra red. La idea es hacer uso de powershell y SQL Server para crearnos este sistema. Aprovecharemos Reporting Services de SQL Server para mostrar gráficamente los resultados obtenidos.
En primer lugar necesitaremos un servidor de BB.DD. con un SQL Server instalado, preferiblemente con una versión 2005 o superior (aunque también es posible instalar Reporting Services en SQL Server 2000). Crearemos en una base de datos, que en mi caso he denominado ServerFreeSpace. Vamos a crear 4 tablas y un procedimiento almacenado:

El procedimiento almacenado, RegSpaceImport, nos servirá para coger un fichero de texto plano (un .txt) que va a contener un conjunto de líneas que recogen, cada una de ellas:

• el nombre del servidor,
• la unidad de disco,
• el espacio total de la unidad de disco,
• el espacio usado de esa unidad y
• el espacio libre.

Este fichero txt es el resultado de un script en powershell que veremos más adelante.
El código de este procedimiento almacenado es el siguiente:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_RegSpaceImport] @PathFileName varchar(100), @FechaHoy datetime AS --Paso 1: Construir una clausula BULK INSERT valida DECLARE @SQL varchar(2000) -- Formato valido (ejemplo): NombreServidor,C:,33.91,6.59,27.32 SET @SQL = "BULK INSERT TmpRegSpace FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') " --Paso 2: Ejecutar la clausula BULK INSERT EXEC (@SQL) --Paso 3: INSERT datos al final de la tabla REGSPACE INSERT RegSpace (NServ,NUnidad,TotalEspacio,EspacioUsado,EspacioLibre,FechReg) SELECT NServ,NUnidad,TotalEspacio,EspacioUsado,EspacioLibre,@FechaHoy FROM TmpRegSpace --Paso 4: Vaciar la tabla temporal TRUNCATE TABLE TmpRegSpace

Este procedimiento almacenado deberemos utilizarlo en un trabajo del Agente de SQL Server, para que sea ejecutado a diario:

Lo que haremos en este trabajo es lanzar el procedimiento almacenado descrito, la ruta en la que queremos que se almacene el fichero .txt resultante y la fecha de ejecución:

Por otra parte, las cuatro tablas que deberemos crear en nuestra BB.DD. son las siguientes:

• Tabla RegSpace: es la tabla principal en la que se almacenaran los datos obtenidos junto a la fecha.

• Tabla TDiscosServ: tabla en la que se almacena las unidades de disco que tiene cada servidor. La emplearemos en un dropdownlist del Reporting Services.

• Tabla TmpRegSpace: se trata de una tabla intermedia que utilizará el procedimiento almacenado para insertar los datos obtenidos del fichero txt. Si nos fijamos en el procedimiento almacenado, primero hacemos una inserción masiva (BULK INSERT, paso 2) en TmpRegSpace, y posteriormente utilizamos estos datos para insertar en la tabla RegSpace (paso 3) añadiendo la fecha.

• Tabla TServidores: tabla que almacena los distintos servidores existentes. La emplearemos para llenar un dropdownlist del Reporting Services. 

Una vez que hemos visto la BB.DD. que vamos a emplear, pasamos a ver el script en powershell. Este script será ejecutado a través de una tarea programada en una máquina donde tengamos instalado Powershell (sólo en W2008 R2 ya viene incluido). Si no lo tenemos instalado, podemos obtenerlo desde aquí . Yo he empleado la versión 1.0 que era la que tenía instalada en un W2003.

La tarea programada lanzará a diario este script en powershell, de la siguiente forma:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe C:\InformeEspacioDisco\ReportBBDDEspacio.ps1
Indicando la ruta donde se encuentra instalado Powershell, así como la ruta de nuestro script (en mi caso lo he denominado ReportBBDDEspacio.ps1). El script debe ejecutarse antes que el procedimiento almacenado RegSpaceImport de SQL Server.
El script deberá ejecutarse desde una máquina que tenga acceso a los diferentes servidores que queremos analizar y empleará dos ficheros de texto plano (.txt). Uno de salida (fichero de resultados), que en mi caso he ubicado en una carpeta compartida en el fichero de BBDD, y otro fichero que contiene los nombres de los servidores cuyas unidades de disco queremos analizar.
Una primera opción es no especificar un usuario con privilegios en el código del script (por lo que se empleará el usuario indicado en la tarea programada):

#Fichero de resultados $freeSpaceFileName = file://nombre_servidor_bbdd/InformeEspacioDisco/EspacioLibre.txt #Fichero con los servidores analizados (un servidor por linea) $serverlist = "C:\InformeEspacioDisco\ListaServidores.txt" New-Item -ItemType file $freeSpaceFileName -Force Function writeDiskInfo { param( $fileName,$servidor,$devId,$frSpace,$totSpace) $totSpace=[math]::Round(($totSpace/1073741824),2) if ($totSpace -eq 0) {     $totSpace = 1 } $frSpace=[Math]::Round(($frSpace/1073741824),2) $usedSpace = $totSpace - $frspace $usedSpace=[Math]::Round($usedSpace,2) $linea = $servidor+","+$devid+","+$totSpace+","+$usedSpace+","+$frSpace Add-Content $fileName $linea } foreach ($server in Get-Content $serverlist) { $dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3} foreach ($item in $dp) { Write-Host $server $item.DeviceID $item.FreeSpace $item.Size writeDiskInfo $freeSpaceFileName $server $item.DeviceID $item.FreeSpace $item.Size } }

Pero también podemos indicar el usuario en el propio código, encriptando el password previamente y almacenándolo en un fichero de texto (Credencial.txt). Esto nos puede ser de utilidad si tenemos varios dominios y no existen relaciones de confianza entre ellos.

#Fichero de resultados $freeSpaceFileName = file://nombre_servidor_bbdd/InformeEspacioDisco/EspacioLibre.txt #Fichero con los servidores analizados (un servidor por linea) $serverlist = "C:\InformeEspacioDisco\ListaServidores.txt" New-Item -ItemType file $freeSpaceFileName -Force Function writeDiskInfo { param( $fileName,$servidor,$devId,$frSpace,$totSpace) $totSpace=[math]::Round(($totSpace/1073741824),2) if ($totSpace -eq 0) {     $totSpace = 1 } $frSpace=[Math]::Round(($frSpace/1073741824),2) $usedSpace = $totSpace - $frspace $usedSpace=[Math]::Round($usedSpace,2) $linea = $servidor+","+$devid+","+$totSpace+","+$usedSpace+","+$frSpace Add-Content $fileName $linea } foreach ($server in Get-Content $serverlist) { $pw = Get-Content C:\InformeEspacioDisco\Credencial.txt | ConvertTo-SecureString $cred = New-Object System.Management.Automation.PsCredential("Dominio\usuario", $pw) $dp = Get-WmiObject win32_logicaldisk -ComputerName $server -Credential $cred | Where-Object {$_.drivetype -eq 3} foreach ($item in $dp) { Write-Host $server $item.DeviceID $item.FreeSpace $item.Size writeDiskInfo $freeSpaceFileName $server $item.DeviceID $item.FreeSpace $item.Size } }

Con estos pasos ya hemos completado el primer paso del proyecto, la obtención de los datos y su almacenado en una BB.DD. En el próximo artículo veremos cómo mostrar esta información haciendo uso de Reporting Services.