Table of Contents

Estadísticas de discos locales en SQL Server

Este script de PowerShell es útil para recopilar datos sobre el espacio en disco de los dispositivos lógicos en un servidor y almacenarlos en una tabla de SQL Server para su posterior análisis. Puede ser programado fácilmente en el Task Scheduler para ejecutarlo periódicamente y mantener actualizada la información. Sin embargo, algunas limitaciones incluyen la necesidad de configurar correctamente las credenciales de SQL Server y la autenticación, así como asegurar que las conexiones TCP/IP estén habilitadas. Además, debido a la complejidad y variabilidad de los entornos de SQL Server, pueden surgir desafíos adicionales en la configuración y mantenimiento del script. No obstante, una vez establecido, los datos recopilados pueden ser utilizados en herramientas de visualización como PowerBI para crear tableros informativos y tomar decisiones informadas sobre la gestión del almacenamiento y la infraestructura.

Creación de Usuario y Tabla en SQL Server

Script de Creación de la Tabla: 

USE TEST;
GO
 
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'hddstats')
BEGIN
    CREATE TABLE hddstats (
        id INT IDENTITY(1,1) PRIMARY KEY,
        TIMESTAMP DATETIME,
        hostname NVARCHAR(255),
        deviceID NVARCHAR(50),
        FreeSpace FLOAT,
        SIZE FLOAT,
        UsedSpace FLOAT
    );
END;

Script de PowerShell (para Insertar Datos):

# Definir las credenciales y la cadena de conexión a la base de datos
$serverName = "sql.ad.facundoitest.space"
$databaseName = "TEST"
$username = "svcSQL"
$password = "tu_contraseña"
 
# Obtener el hostname del servidor
$hostname = $env:COMPUTERNAME
 
# Crear la cadena de conexión
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$username;Password=$password;"
 
# Obtener los datos utilizando el script existente
$data = Get-WmiObject -Class Win32_LogicalDisk | Select-Object DeviceID, VolumeName, @{Name="FreeSpace_GB";Expression={[math]::Round($_.FreeSpace / 1GB, 2) } }, @{Name="Size_GB";Expression={[math]::Round($_.Size / 1GB, 2)}}, @{Name="UsedSpace_GB";Expression={[math]::Round(($_.Size - $_.FreeSpace) / 1GB, 2)}}
 
# Conectar a la base de datos
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
 
# Crear el comando SQL para insertar los datos en la tabla
foreach ($row in $data) {
    $commandText = "INSERT INTO hddstats (timestamp, hostname, deviceID, FreeSpace, Size, UsedSpace) VALUES (GETDATE(), '$hostname', '$($row.DeviceID)', $($row.FreeSpace_GB), $($row.Size_GB), $($row.UsedSpace_GB))"
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $connection
    $command.CommandText = $commandText
    $command.ExecuteNonQuery()
}
 
# Cerrar la conexión
$connection.Close()

Consideraciones:

  1. El usuario utilizado en la conexión es de SQL (no Windows).
  2. Antes de ejecutar el script, asegúrate de habilitar las conexiones TCP/IP desde el Configuration Manager de SQL Server.
  3. Además, es necesario habilitar el login de usuarios de Windows y SQL desde 'Propiedades > Seguridad' de la base de datos en SQL Server Management Studio.