Code: Select all
clear-host
Add-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
try
{
$DBservers = ('ServerFQDN')
#Clear-Variable -name Alert -Scope Global
#$DBservers = ('ServerFQDN','ServerFQDN\SQL2017')
foreach ($DBServer in $DBservers)
{
$QueryText1 = "select name from sys.databases where state_desc = 'online'"
$DBname = Invoke-SQLCMD $QueryText1 -ServerInstance $DBServer
if ($DBname -eq $null)
{
$QueryText4 = "select '$DBServer' as SQLInstance, name as DB_name, state_desc from sys.databases where state_desc <> 'online' and state_desc <> 'RESTORING' "
$DB = Invoke-SQLCMD $QueryText4 -ServerInstance $DBServer
if ($DB.name -eq $null)
{
}
else
{
$R3 = "Issue found, below are the details"
Write-Host "Issue found, below are the details"
$DB | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Format-Table | Out-File $output -append
$Alert = '1'
}
}
else
{
$databases = $DBname.Name
$R3 = "Issue found, below are the details"
foreach ($database in $databases)
{
$QueryText2 = "select '$DBServer' as SQLInstance, name as DB_name, state_desc from sys.databases where state_desc = 'online' and name = '$database' "
$Result = Invoke-SQLCMD $QueryText2 -ServerInstance $DBServer
If ($Result -eq $null)
{
exit 3
}
Else
{
$Result | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors
$Result
#| Out-File -FilePath $output -append
#| Format-Table
$Alert = '1'
}
}
}
}
If ($Alert -eq '1')
{
$R2 = "Problem occured"
exit 2
}
else
{
$R4 = "No Issue:All Databases Are Online"
exit 0
}
}
catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Host "Error:$ErrorMessage $FailedItem"
break
}
I'm struggling to print the complete result of powershell output on nagios, i made a powershell script to show the databases status but result displayed on nagios of only 13 lines, actually result have 21 lines.
Note: I have already set the timeout of 180 sec on both nsclinet.ini and check_nrpe side.
command alias:
check_db_status = cmd //T:180 /c echo scripts\DB_Status.ps1; exit $LastExitCode | powershell.exe -command -
Powershell script as shared below, Please suggest
