Project Objective:
Monitor remote connection to Microsoft SQL Server can be deployed in a multiple scenarios.

REQUIRED:
Requires a local SQL user account to be used with SQL Authentication. Local account must have CONNECT permissions on each database.

Problem to be solved:
All too often I see people just check to see IF a MSSQL server is answering on its port as a way of determining it is online. Some others have the MSSQL server query itself locally which is a great first step but if your SQL server is in production and its primary use is to serve queries originating from other systems than THAT should be your test not that it can just answer a query locally.

Future Versions:
I’d like to add the SMTP auth ability, when I get around to it.

Notes:

At the moment there is no error detection built in for the email send. Also, note that the email’s subject and body are the same content. Long story short I need the script now!

#
# Watch-MSSQL.ps1
#
# Written by Aaron Wurthmann (aaron (AT) wurthmann (DOT) com)
#
# If you edit please keep my name as an original author and
# keep me apprised of the changes, see email address above.
# This code may not be used for commercial purposes.
# You the executor, runner, user accept all liability.
# This code comes with ABSOLUTELY NO WARRANTY.
# You may redistribute copies of the code under the terms of the GPL v2.
# -----------------------------------------------------------------------
# 2011.08.31 ver 2
#
# Summary:
# Sends a remote SQL query to Microsoft SQL server databases.
# Can be configured to send an email in non-Nagios environments. 
#
# REQUIRED:
# Requires a local SQL user account to be used with SQL Authentication. 
# Local account must have CONNECT permissions on each database.
#    In Query Editor, enter the following Transact-SQL command: 
#        CREATE LOGIN <login name> WITH PASSWORD = '<password>' ; GO
#        USE <database>; GRANT CONNECT TO <login name> ; GO
#     From: http://msdn.microsoft.com/en-us/library/aa337562.aspx
#          http://msdn.microsoft.com/en-us/library/ms178569.aspx
#     
# -----------------------------------------------------------------------
# General Usage:
#    This script can be edited and or parameters can be passed to enable
#    email alerts, to whom, from whom, using what server, etc.
#     Examples:    
#        .\Watch-MSSQL.ps1 -email True -From foobar@null.local -To administrator@null.local -Server smtp.null.local
#
# Scheduled Task Usage
#    To run this script as a scheduled task create a .bat .cmd file
#    As indicated above you can either pass the needed parameters or
#    edit Watch-MSSQL.ps1 itself. 
#    Example of .bat or .cmd file:
#        powershell -command "& '.\Watch-MSSQL.ps1' -email True -From foobar@null.local -To administrator@null.local -Server smtp.null.local"
#
# Nagios Usage:
#    For Nagios NRPE/NSClient++ usage add the following line to the 
#    NSC.ini file after placing this script in Scripts subdirectory.
#    check_mssqlquery=cmd /c echo scripts\Watch-MSSQL.ps1; exit($lastexitcode) | powershell.exe -command -
#    NOTE: The trailing - is required.
# -----------------------------------------------------------------------
# Notes:
#    At the moment there is no error detection built in for the email send.
#    There is also no smpt auth. I'll put that in at a later time.
#     Also, note that the email's subject and body are the same content,
#        This is due to me removing the email fuctionality and errors messages
#        then later adding the email fuctionality back only.
# -----------------------------------------------------------------------

Param(
    $Debug=$False,
    [string]$From = 'noreply@domain.ext',
    [string]$To = 'someone@domain.ext',
    [string]$Server = 'smtp.domain.ext',
    $email=$false
)

# Editable Enviroment Settings
[string]$SQLServer='sql.domain.ext'
[int]$SQLPort=1433
[string]$Username='local_sql_useranme'
[string]$password='password'
$Databases=@(
    'database0',
    'database1';
)

# Static Parameters
[string]$Query = "SELECT DB_NAME() AS DataBaseName"
[string]$computername=$env:computername 

function Required-SnapIns {
    try {
        Add-Pssnapin SqlServerProviderSnapin100 -ErrorAction Stop
        Add-Pssnapin SqlServerCmdletSnapin100 -ErrorAction Stop
        return $true
    } catch {
        [string]$ErrorString = [string]$_.Exception.Message
        if ($ErrorString.contains("already added")) {
            return $true
        } else {
            Write-Host $_.Exception.Source,":", $ErrorString
            return $false
        }
    }
}

function Get-SQLQuery {
    Param([string]$SQLServer,[string]$Database,[string]$Query)
    if (($username) -and ($password)) {
        $Results = Invoke-SQLCmd -ServerInstance $SQLServer -Database $Database -ConnectionTimeout 300 -QueryTimeout 600 -Query $Query -Username $Username -Password $Password
    }
    ELSE {
        $Results = Invoke-SQLCmd -ServerInstance $SQLServer -Database $Database -ConnectionTimeout 300 -QueryTimeout 600 -Query $Query
    }
    return $Results
}

function Test-Port{
    Param([string]$server,$port,$timeout=300)    
    $ErrorActionPreference = "SilentlyContinue"
    $tcpclient = new-Object system.Net.Sockets.TcpClient
    $iar = $tcpclient.BeginConnect($server,$port,$null,$null)
    $wait = $iar.AsyncWaitHandle.WaitOne($timeout,$false)
    if(!$wait)
    {
        $tcpclient.Close()
        Return $false
    }
    else
    {
        $error.Clear()
        $tcpclient.EndConnect($iar) | out-Null
        Return $true
        $tcpclient.Close()
    }
}

# Debug
if (!$Debug) { $ErrorActionPreference="SilentlyContinue" }

if ($email) {
    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient($Server)
    $msg.From = $From
    $msg.To.Add($To)
}

$Install=Required-SnapIns
if (!$Install){
    $Results = "ERROR: Required SnapIns not present."
    write-host $Results
    if ($email) {
        $msg.Subject = $Results
        $msg.Body = $Results
        $smtp.Send($msg)
    }
    exit 1
}

$SQLServerUp1=Test-Port $SQLServer $SQLPort
if (!$SQLServerUp1){
    $SQLServerUp2=Test-Port $SQLServer $SQLPort
    if (!$SQLServerUp2){
        $Results="CRITICAL ERROR: SQL Server " + $SQLServer + " did not respond on port " + $SQLPort + " from " + $computername
        write-host $Results
        if ($email) {
            $msg.Subject = $Results
            $msg.Body = $Results
            $smtp.Send($msg)
        }
        exit 2 
    }
}

ForEach ($Database in $Databases) {
    $QueryResults=Get-SQLQuery $SQLServer $Database $Query
    if (!$QueryResults){
        if ($Results) {
            $Results=$Results + " and " + $Database
        }
        ELSE {
            $Results="CRITICAL ERROR: Database(s) Offline: " + $Database
        }
    }
    
    if ($CheckedDatabases) {
    $CheckedDatabases=$Database + ", " + $CheckedDatabases
    }
    ELSE {
        [string]$CheckedDatabases=$Database
    }
}

if ($Results) {
    write-host $Results
    if ($email) {
        $msg.Subject = $Results
        $msg.Body = $Results
        $smtp.Send($msg)
    }
    exit 2
}
ELSE {
    write-host "All database(s) are online. Database(s) checked:" $CheckedDatabases
    exit 0
}
Be Sociable, Share!