PoSH & SQL : Monitor and Report Disk Usage Stats - Task Scheduler
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 activity 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 initial 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
}
}
}
Code
<#
.SYNOPSIS
Name : Disk Space Utilization Report (Get-DiskSpace.ps1)
Description : Get disk space usage information from remote server(s) with WMI and output 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 initial 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
}
}
}
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
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