Abstract
A big part of the operational responsibilities is to monitor and maintain the health of running servers. If a production server goes down because of various reason, appropriate actions must be undertaken to bring the server back to life. However, before any resurrection, first and foremost important things is to check for the system resources. Monitoring disk space is essential to improve application availability and Server availability. Have written series of article to measure disk usage metrics based on two categories. Real-time monitoring is concerned with measuring the current state and provide an output in some means.Log-based monitoring deals with logging the current state into a database and the information can be used for capacity planning and forecasting.

Introduction

This article shows one possible solution for monitoring disk space usage using ADO.NET class libraries in PoSH and SQL Server for storage, execute the script via Windows Task Scheduler. Low free space on a drive is also one of the reasons for file system fragmentation which causes severe performance issues. This is one of the important metric has to be measured in every environment. The disk usage details are stored in a SQL repository. The input list of the servers is fed from a text file or the CSV file. The script also has a provision to log the activity log. 

Overview

This section describes the pre-requisites and setup Windows Task Scheduler and other instruction to implement this process

How to create a table

  • Connect to the Database Engine SSMS
  • From the Standard bar, click New Query
  • Copy and paste the following example into the query window and then click Execute
The example creates a table with default constraint

CREATE TABLE [dbo].[DiskSpace](
    [server] varchar(20) NOT NULL,
    [driveLetter] [varchar](5) NULL,
    [volumeName] varchar(25),
    [capacity] [int] NULL,
    [freeSpace] [int] NULL,
    [logdate] varchar(10) default convert(varchar(10),getdate(),112)
    )
    
   
select Server,
DriveLetter,
volumeName,
Capacity,
FreeSpace
cast(freeSpace/cast(capacity as float) as decimal(5,2))*100 '%Free',
logdate,
from [DiskSpace]

Use ADO.NET objects


Te ADO.NET has been part of .NET since the beginning and PowerShell can access .NET objects, no additional objects are needed to execute a database query.The more commonly used classes available in the System.Data.SqlClient and System.Data namespaces. The System.Data.SqlClient namespace includes the following connected classes specific to SQL Server:

SqlConnection


Connects to the SQL Server .NET data provider in order to establish and manage the connection to the target database.

Function Connect-Database($connString){
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connString
    return $sqlConnection
}

SqlCommand

Contains the details necessary to issue a T-SQL command against a SQL Server database.

Function Insert-IntoDatabase($sqlCommand, $query){      
    $sqlCommand.CommandText = $query
    try{
        $sqlCommand.executenonquery() | Out-Null
    }
    catch {
        $_.Exception
    }
}

CreateCommand()

Creates and returns a SqlCommand object associated with the SqlConnection.


Write-Log


Write-Log writes a message to a specified log file along with the current time stamp also writes state of the message(Information, Warning or Error).
For example, The first example writes a simple message with a default state to a log file abc.log. In the second example, a message along with "Error" state details are entered into the log file.

1..EXAMPLE 1
2.PS:\> Write-Log  -Message "Server is reachable and starting the process " -Logfile c:\PowerSQL\abc.log
3..EXAMPLE 2
4.PS:\> Write-Log  -level Error -Message "Server is not reachable " -Logfile c:\PowerSQL\abc.log

The below function can be reused to in any of the PoSH code. Also, the output file will be used for troubleshooting and acvitiy progress tracking.

Function Write-Log {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory=$False)]
    [ValidateSet("INFO","WARN","ERROR","FATAL","DEBUG")]
    [String]
    $Level = "INFO",
 
    [Parameter(Mandatory=$True)]
    [string]
    $Message,
 
    [Parameter(Mandatory=$False)]
    [string]
    $logfile
    )
 
    $Stamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
    $Line = "$Stamp $Level $Message"
    If($logfile) {
        Add-Content $logfile -Value $Line
    }
    Else {
        Write-Output $Line
    }
}

Data Manipulation and Prepare Insert statement


This section explains how to define connection string and how to iterate over group of listed servers.  
  • Builds connection string based on the input parameters. This is needed because the data will be written back to the table. The connection string refers to the central server and initiall catalog which defines the databases
  • Querying Get-Win32LogicalDisks 
  • Assigning manipulated data to its corresponding variable
  • Prepare Insert statement
  • Execute the query, it will feed the manipulated data into the table

$connString = "Data Source=$dataSource; Initial Catalog=$InitialCatalog; Integrated Security=True"
$sqlConnection = Connect-Database $connString
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
  
import-csv $InputCSV|%{
 
 If (!(Test-Connection $_.Server -count 1 -quiet)) {
Write-Log  -Message "$($_.Server) is not reachable" -Logfile $Logfile
}
else
{
       $items = ""     
       $items = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $_.server -filter "drivetype=3"
       Write-Log  -Message " Inserting $($_.Server) disk information ($_.Server)" -Logfile $Logfile
        foreach($item in $items) {
            $deviceID = $item.DeviceID
            $VolumeName=$item.VolumeName           
            $size = [math]::round(($item.Size)/1024/1024/1024, 0
            $freeSpace = [math]::round(($item.FreeSpace)/1024/1024/1024, 0)  
             
            $Insert = "INSERT INTO [DiskSpace] (server,volumeName,driveLetter,capacity,freeSpace) VALUES ('$($_.server)','$VolumeName','$deviceID','$size','$freeSpace')"
            Insert-IntoDatabase $sqlCommand $Insert
 }   
        }
}

↑ Return to Top


Code

<#
  
.SYNOPSIS      
    Name :  Disk Space Utilizaiton Report (Get-DiskSpace.ps1)
    Description : Get disk space usage informations from remote server(s) with WMI and ouput the data to a table
    
    Author : Prashanth Jayaram
       
    * Select list of servers from a CSV file or text file
    * Get remote Servers informations with WMI and Powershell
    * Disk (Disk type, letter, capacity in GB, free space in GB, % free , Status )
    * Log the details in activity log file
      
       
.INPUT
    .csv file
   
.OUTPUTS
    SQL Table
    Console outputs : You can alter the code to write the data to file or console  
   
.NOTES
    Version:        1.0
    Author:         Prashanth Jayaram
    Creation Date:  2016-10-13
    Purpose/Change: Initial script development
     
.EXAMPLE
    .\Get-DiskSpace.ps1
#>
  
  
#########################################################################################
  
#### Input CSV, output directory to save Log file, datasource and Intital catalog to establish connection with the servers
  
param (
    [Parameter(Mandatory=$true)][string]$InputCSV,
    [Parameter(Mandatory=$true)][string]$DirectorytoSaveTo,
    [Parameter(Mandatory=$true)][string]$dataSource,
    [Parameter(Mandatory=$true)][string]$InitialCatalog
 )
 
   
# formatting the date
  
 $date=Get-Date -format "yyyy-MM-d"
   
 #Prepare log file
   
 $LogFileName="DiskSpaceLog_$($date)"
    
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
  
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
  {
  New-Item "$DirectoryToSaveTo" -type directory | out-null
  }
     
# check the existence of log file, If the log file doesn't exist, then create it 
  
$logfile = "$DirectoryToSaveTo$LogFileName.log"
  
if (!(Test-Path -path "$logfile")) #create it if not existing
  {
   New-Item -ItemType file $logfile -Force
  }
  
# Prepare headers for the log file for each execution of script
  
Add-Content $logfile "#################################################################"
Add-Content $logfile "Disk Space Details"
Add-Content $logfile "Generated $(get-date)"
Add-Content $logfile "Generated from $(gc env:computername)"
Add-Content $logfile "#################################################################"
   
 
<#
Add's timestamps - can't have a log without timestamps.
Add's a level (uses INFO by default) meaning you can highlight big issues.
#>
Function Write-Log {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory=$False)]
    [ValidateSet("INFO","WARN","ERROR","FATAL","DEBUG")]
    [String]
    $Level = "INFO",
 
    [Parameter(Mandatory=$True)]
    [string]
    $Message,
 
    [Parameter(Mandatory=$False)]
    [string]
    $logfile
    )
 
    $Stamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
    $Line = "$Stamp $Level $Message"
    If($logfile) {
        Add-Content $logfile -Value $Line
    }
    Else {
        Write-Output $Line
    }
}
 
 
 Function Connect-Database($connString){
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connString
    return $sqlConnection
}
 
Function Insert-IntoDatabase($sqlCommand, $query){      
    $sqlCommand.CommandText = $query
    try{
        $sqlCommand.executenonquery() | Out-Null
    }
    catch {
        $_.Exception
    }
}
#define Connection string
$connString = "Data Source=$dataSource; Initial Catalog=$InitialCatalog; Integrated Security=True"
$sqlConnection = Connect-Database $connString
#open the connection
$sqlConnection.Open()
#Creates and returns a SqlCommand object associated with the SqlConnection.
$sqlCommand = $sqlConnection.CreateCommand()
  
import-csv $InputCSV|%{
 
 If (!(Test-Connection $_.Server -count 1 -quiet)) {
Write-Log  -Message "$($_.Server) is not reachable" -Logfile $Logfile
}
else
{
       $items = ""     
       $items = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $_.server -filter "drivetype=3"
       Write-Log  -Message " Inserting $($_.Server) disk information ($_.Server)" -Logfile $Logfile
        foreach($item in $items) {
            $deviceID = $item.DeviceID
            $VolumeName=$item.VolumeName           
            $size = [math]::round(($item.Size)/1024/1024/1024, 0
            $freeSpace = [math]::round(($item.FreeSpace)/1024/1024/1024, 0)  
             
            $Insert = "INSERT INTO [DiskSpace] (server,volumeName,driveLetter,capacity,freeSpace) VALUES ('$($_.server)','$VolumeName','$deviceID','$size','$freeSpace')"
            Insert-IntoDatabase $sqlCommand $Insert
 }   
        }
}

↑ Return to Top


Task Scheduler 

 
How to create an event using  task scheduler:

  •  Open "Task Scheduler" (Goto START—RUN —Type Tasks and hit enter)
  •  Click "Create task"
  •  Pick a name, and choose "Run whether user is logged on or not"

  • Choose "Triggers" Tab, Click "New"
  • Specify option you like, and then click "OK" to create a trigger

 

  • Choose "Actions" tab, Click "New"
  • Copy following command to "Program/script" textbox, click C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
  • Enter the path of the saved script file in "Add arguments (optionally)" textbox
As per the screenshot, I saved the file under C:\Perform_script.ps1 hence I updated the add arguments text box as

F:\DiskSpace.ps1; exit 

Output

Invoking the PoSH script from Task scheduler 


Or

You can call the PowerShell script file directly from the PowerShell ISE console


↑ Return to Top


Conclusion

Disk Monitoring is one of the key metrics to provide a total picture of what is going on with your server. By collecting Free Space key metrics in a repository provides a easy way to do forecasting, capacity planning and reporting

  • Explains the use ADO.Net class libraries
  • Central repository for reporting
  • Rich data set leads to easy forecasting and capacity planning
  • The use of Windows Task Scheduler 

 ↑ Return to Top



References

↑ Return to Top


See Also

  1. PoSH - Step by Step - Disk Space Monitoring Guide