Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 

Comments

Leave a comment on the original post [jason-carter.net, opens in a new window]

Loading comments...
 

Jason Carter

Jason Carter has spent most of his career as a .NET developer, with time spent as a development manager, accidental DBA, and most recently a full-time DBA. Having worked with large databases as a developer, he found great interest in tuning, tweaking, and making databases run faster. With the support of his wife, he gave up his managerial duties, jumped the development ship and dove head first into his new career as a Database Administrator.

Disk Space Checker

One method my company deploys our software is in a dedicated hosted environment. In this situation, we partner with a data center that provides hardware, we install the software and the client doesn’t get access to the hardware directly. This normally works well, unless they tend to have a-lot of activity which could cause the disk to fill prematurely. (Tons of side conversations could be had here about proper sizing, alternative storage methods, etc, but that’s for another day)

Not wanting to go into each system daily and check the available space, I whipped up some Powershell to do the work for me.

A few features I put in here:

  1. Emailed Report
  2. No Credentials Easily Accessible
  3. Only Send if below threshold

Setup

First we import our Net.Mail name space and setup a few user configurable variables.

; html-script: false ]
[System.Reflection.Assembly]::LoadWithPartialName("Net.Mail")

#####################
##  USER SETTINGS ###
#####################
$emailSubject = "Server Alert - Disk Space Low"     # Email Subject
$supportEmail = "support@yourdomain.com"            # Email to List as Contact 
$supportText = "My Support"                         # Text Name of Email Group Displayed in Email
$users = "support@yourdomain.com"                   # List of users to email your report to (CSV)
$ccusers = ""                                       # Address to CC (CSV)
$bccusers = ""                                      # Address to BCC (CSV)

$CredsFile = "c.pjc"                                # File Name to Store Encrypted Credentials
$ServerFile = "servers.txt"                         # File Name of Servers to Check
                                                    # Put one server on each Line in this file
[decimal]$thresholdspace = 10                       # Disk space threshold below in percent

Basic Checks

In this section, we get the path to the script itself, and look in that same directory for the Credentails and Servers files. If the credentials file does not exists, it will prompt you for the details, which it will then encrypt and save.

; html-script: false ]
$Path = $MyInvocation.MyCommand.Path
$Folder = Split-Path $Path
$FileExists = Test-Path "$Folder\$CredsFile"
if  ($FileExists -eq $false) {
    $email = Read-Host "Enter Email" 
    $password = Read-Host "Enter Password"
    $server = Read-Host "Enter Host" 
    $estring = ConvertTo-SecureString "$password|$server|$email" -AsPlainText -Force
    ConvertFrom-SecureString $estring | Out-File $CredsFile
} else {
    Write-Host "Credential File Found"
}

$ServersExists = Test-Path "$Folder\$ServerFile"
if ($ServersExists -eq $false) {
    Write-Host "You Must Specify a Server File"
    return
} else {
    Write-host "Server File Found"
}

Extract the Secrets

This section is where we decrypt the credentials for our email server, as well as read the names of the servers from the $Server file.

; html-script: false ]
$estring = Get-Content $CredsFile | ConvertTo-SecureString
$newstring = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($estring))
$sArray  = $newstring.split("|")
$pw = $sArray[0] 
$server = $sArray[1]
$fromemail = $sArray[2]
$login = $fromemail
$port = 587
$computers = get-content "$Folder\$ServerFile"     #Grab the names of the servers the server file.
</p>

<h4>Do It</h4>
This is the one line that does the actual data gathering.
There is alot going on here, but if you follow it piece by piece you can understand what its doing.
<ol><li>Uses Get-WMIObject to query the computers, looking at the Win32_LogicalDisk object
<li>Selects a few variables from that object (formating a few in the process)
<li>Filters the list looking only at those above our threshold.
<li>Converts the resulting data into a table fragment for later use.
</ol>
<pre class="brush: powershell; gutter: true; first-line: 1; highlight: []; html-script: false">
$tableFragment= Get-WMIObject  -ComputerName $computers Win32_LogicalDisk `
| select __SERVER, DriveType, Name, @{n='Size (Gb)' ;e={"{0:n2}" -f ($_.size/1gb)}},@{n='FreeSpace (Gb)';e={"{0:n2}" -f ($_.freespace/1gb)}}, @{n='PercentFree';e={"{0:n2}" -f ($_.freespace/$_.size*100)}} `
| Where-Object {$_.DriveType -eq 3 } ` #-and [decimal]$_.PercentFree -lt [decimal]$thresholdspace} `
| ConvertTo-HTML -fragment

Build the Email

Here we have some predefined HTML and CSS, which you could easily save to another file, but for me it made sense to just have it as one scrit.

; html-script: false ]
$HTMLmessage = @"
<head>
<style type=""text/css"">
body {margin: 1% 3% 1% 3%; font: .8em/.8em ""Century Gothic"",""Trebuchet MS"",Arial,Helvetica,sans-serif;}
table {width:95%;border-top:2px solid #e5eff8;border-right:2px solid #e5eff8;margin: 1em 0 1em 0;border-collapse:collapse;}
tr.odd td   {background:#0099FF}
td {color:#678197;border-bottom:1px solid #e5eff8;border-left:1px solid #e5eff8;padding:.1em .5em;text-align:center;}               
th {background:#0099FF;color:#FFFFFF;border-bottom: 1px solid #e5eff8;border-left:1px solid #e5eff8;padding:.1em .3em;text-align:center;font:bold 1.0em/1.0em ""Century Gothic"",""Trebuchet MS"",Arial,Helvetica,sans-serif;white-space:nowrap;}                           
</style>
</head>
<body BGCOLOR=""white"">
<h3>Disk Space Storage Report</h3>
<p>The below drives fall below the $thresholdspace % free space threshold. You should free up space on this server immediately.
$tableFragment
<P>Any questions regarding this alert should be forwarded to <a href=""mailto:$supportEmail"">$supportText</a>
</body>
"@

Send it Or Not?

Notice in this snippet, the conditional statement uses Regex to search our $HTMLMessage for any

. If we see that tag, we know we have data and we can send our email using the standard Net.Mail.SmtpClient using the credentials stored in our credentials file we decrypted above.

; html-script: false ]
$regexsubject = $HTMLmessage
$regex = [regex] '(?im)<td>'

# if there was any row at all, send the email
if ($regex.IsMatch($regexsubject)) {
                        $Client = new-object Net.Mail.SmtpClient($server, $port)
                        $Client.EnableSsl = $true
                        $Client.Credentials = new-object System.Net.NetworkCredential($login, $pw);
                        $message = New-Object System.Net.Mail.MailMessage $fromemail, $users
                        if ($ccusers) {
                            $message.CC.Add($ccusers)
                        }
                        if ($bccusers) {
                            $message.BCC.Add($bccusers)    
                        }
                        
                        $message.Subject = $emailSubject
                        $message.IsBodyHTML = $true
                        $message.Body = $HTMLmessage
                        #$Client.Send($message)
                        Write-Host "Alert Email Sent"
}