Archive for August, 2011

PowerShell Script Watch-Exchange-Queues.ps1

Project Objective:
Monitor Exchange 2010 as well as Exchange 2003 message queues. This script can be deployed in a multiple scenarios with our without a Nagios or other monitoring enviroment.

Problem to be solved:
Increased monitoring of Exchange Server message queues. Get notified when messages are not leaving message queues.

Suggestions:
Monitor Exchange 2010 with native EMS Get-Queue command. ā€“ REJECTED, Too slow for monitoring system.
Monitor Exchange 2003 with previous VisualBasic Script check_msmq.wsf ā€“ REJECTED, One ring to rule them all!

#
# Watch-Exchange-Queues.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 3
#
# Summary:
# Checks number of messages in Exchange 2003 or 2010 message queues. If warning or
# critical thresholds are reached number of messages are outputted.
# -----------------------------------------------------------------------
# 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-Exchange-Queues.ps1
#        .\Watch-Exchange-Queues.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-Exchange-Queues.ps1 itself. 
#    Example of .bat or .cmd file:
#        powershell -command "& '.\Watch-Exchange-Queues.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_exch_mq=cmd /c echo scripts\Watch-Exchange-Queues.ps1; exit($lastexitcode) | powershell.exe -command -
#    NOTE: The trailing - is required.
# -----------------------------------------------------------------------
# Notes:
#    You may have asked "Why not use a more 'native' method to view the queue in Exchange 2010?"
#     Example: Get-ExchangeServer | where {$_.isHubTransportServer -eq $true} | get-queue | where {$_.MessageCount -gt 0} | measure-object MessageCount -max
#    Because the above requires the Exchange Managment Shell be loaded.
#     The shell loading is time consuming and thus the doesn't lend itself to a Nagios checks.
#    IF you want to go the 'native' route I suggest making a Scheduled Task then monitoring the tasks.
# -----------------------------------------------------------------------

# Optional Parameters and Editable Settings Used to Send E-mail Alert
Param(
    [string]$From = 'noreply@domain.ext',
    [string]$To = 'someone@domain.ext',
    [string]$Server = 'smtp.domain.ext',
    [int]$Port = 25,
    [string]$Username,
    [string]$Password,
    [switch]$email = $false,
    [switch]$SSL = $false
)

# Static Parameters
[int]$WarningLevel = 10
[int]$CriticalLevel = 20
[int]$ResultError = 0
[string]$computername=$env:computername


function Send-EmailMessage {
    param([string]$EmailSubj=$Results, [string]$EmailBody=$Results)
    $EmailMessage.Subject = $EmailSubj
    $EmailMessage.Body = $EmailBody
    $SMTPClient.Send( $EmailMessage )
}

# Exchange 2010 Check
$isHubTransportServer=(Test-Path "HKLM:\SOFTWARE\Microsoft\ExchangeServer\v14\HubTransportRole")
if ($isHubTransportServer) {
    $MessagesQueued=get-wmiobject -query "select MessagesQueuedForDelivery from Win32_PerfFormattedData_MSExchangeTransportQueues_MSExchangeTransportQueues"
    $Messages=$MessagesQueued.MessagesQueuedForDelivery
    if ($Messages) {
        if ($Messages -lt $WarningLevel) {
            $Results = 'No Alert. Number of messages in queue on '+$computername+': ' +$Messages
        }
        if ($Messages -gt $WarningLevel) {
            $ResultError=1
            $Results = 'WARNING ALERT! Number of messages in queue on '+$computername+': ' +$Messages
        }
        if ($Messages -gt $CriticalLevel) {
            $ResultError=2
            $Results = 'CRITICAL ALERT! Number of messages in queue on '+$computername+': ' +$Messages
        }
    }
    Else {
        $ResultError=1
        $Results = 'WARNING ALERT! Message Queue UNAVAILABLE on ' +$computername
    }
}

# Exchange 2003 (If no information or errors from above then check)
if (!($Results)) {
    $isExchange2003Server=get-wmiobject -query "Select Name From Win32_Service Where Name='MSExchangeSA'"
    if ($isExchange2003Server) {
        $ExchangeQueues=get-wmiobject -Namespace "root\cimv2\Applications\Exchange" -Query "SELECT NumberOfMessages FROM ExchangeQueue"
        foreach ($ExchangeQueue in $ExchangeQueues) {
            if (!($Messages)) { $Messages=$ExchangeQueue.NumberOfMessages  } 
            ELSE { $Messages=$Messages + $ExchangeQueue.NumberOfMessages }
        }

        if ($Messages) {
            if ($Messages -lt $WarningLevel) {
                $Results = 'No Alert. Number of messages in queue on '+$computername+': ' +$Messages
            }
            if ($Messages -gt $WarningLevel) {
                $ResultError=1
                $Results = 'WARNING ALERT! Number of messages in queue on '+$computername+': ' +$Messages
            }
            if ($Messages -gt $CriticalLevel) {
                $ResultError=2
                $Results = 'CRITICAL ALERT! Number of messages in queue on '+$computername+': ' +$Messages
            }
        }
        Else {
            $ResultError=1
            $Results = 'WARNING ALERT! Message Queue UNAVAILABLE on ' +$computername
        }
    }
    Else {
        $Results='No Alert. System is not an Exchange 2003 or 2010 Server with SMTP Message Queues.'
    }
}

if ($email) {
    $SMTPClient = New-Object Net.Mail.SMTPClient($Server, $Port)  
    if (($Username) -and ($Password)) { $SMTPClient.Credentials = New-Object System.Net.NetworkCredential($Username, $Password) }
    if ($SSL) { $SMTPClient.EnableSSL = $true }
    $EmailMessage = New-Object System.Net.Mail.MailMessage
    $EmailMessage.From = $From
    $EmailMessage.To.Add( $To )
}

Switch ($ResultError) {
    default {
        write-host $Results
        exit 0
    }

    1 {
        write-host $Results
        if ($email) { Send-EmailMessage }
        exit 1
    }

    2 {
        write-host $Results
        if ($email) { Send-EmailMessage }
        exit 2
    } 
}

PowerShell Script Watch-MSSQL.ps1

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
}