## Script to check MSSQL Server AvailabilityGroup
#Created 10/10/2016 by Vitor Paiva
#
#Usage on NSClient client (must allow check_nrpe):
#check_ag=cmd /c echo scripts\check_ag.ps1;exit($lastexitcode) | powershell.exe -command -
#
#Usage on Nagios Server:
#./check_nrpe -H $HOSTADDRESS$ -p 5666 -t 100 -c check_ag
#
#param([string]$LISTENER , [string]$AG)

#Variable to the AvailabilityGroup LISTENER
$LISTENER = "Insert here name of the AG LISTENER"
#Variable to the AvailabilityGroup Name
$AG = "Insert here name of the Availability group"
#Variable to the Primary Replica Name
$PRIMARY = "Insert here name of the primary replica server name"

## import SQL powershell
Import-Module “sqlps” -DisableNameChecking -cmdlet test-sql*

## check health
cd SQLSERVER:\SQL\$LISTENER\default\AvailabilityGroups

$Results = Test-SqlAvailabilityGroup "$AG" | Select HealthState

if($Results.HealthState -eq "Healthy"){

}else{

$Output = $Results.HealthState + " - Error"
$statFlag = 1
Write-Host $Output
}


if($statFlag -eq 1){
exit 2
}else{

## check replicas

cd SQLSERVER:\SQL\$LISTENER\default\AvailabilityGroups\$AG\Availabilityreplicas

$Results = dir | Test-SqlAvailabilityReplica | Select HealthState

if($Results.HealthState -eq "Healthy"){

}else{

$Output = $Results.HealthState + " - Error"
$statFlag = 1
Write-Host $Output
}


if($statFlag -eq 1){
exit 2
}else{

# check primary

cd SQLSERVER:\SQL\$LISTENER\default\AvailabilityGroups\$AG\Availabilityreplicas
$Results = dir | where {$_.Role -eq "Primary"}

if($Results.Name -eq "$PRIMARY"){

$Output = "AvailabilityGroup is Working Properly"
}else{

$Output = $Results.Name + " - Error - Primary Replica on the Wrong Host "
$statFlag = 1
}
Write-Host $Output

if($statFlag -eq 1){
exit 2
}else{
exit 0
}

}

}