plugins/Invoke-IcingaCheckMSSQLBackupStatus.psm1

<#
.SYNOPSIS
    MSSQL plugin which checks for total backupsize, average backupsize, last backup age,
    last backup log age, last backup execution time, database status of a given database
.DESCRIPTION
    MSSQL plugin which checks for total backupsize, average backupsize, last backup age,
    last backup execution time, database status of a given database
    More Information on https://github.com/Icinga/icinga-powershell-mssql
.FUNCTIONALITY
    MSSQL plugin which checks for total backupsize, average backupsize, last backup age,
    last backup execution time, database status of a given database
.EXAMPLE
    PS>Invoke-IcingaMSSQLBackupOverallStatus -SqlUsername 'username' -SqlPassword (ConvertTo-IcingaSecureString 'password') -SqlHost 'example.com';
    [OK] Check package "MSSQL Backup"
    | 'status'=0;6;5 'size'=10110976b;; 'execution_time'=0s;; 'age'=144000s;; 'average_size'=3370325.333333b;; 'status'=0;6;5 'size'=12664832b;;
    'execution_time'=0s;; 'age'=493200s;; 'average_size'=6332416b;; 'status'=0;6;5 'size'=33445888b;; 'execution_time'=0s;; 'age'=144000s;; 'average_size'=16722944b;;
.EXAMPLE
    PS>Get-IcingaMSSQLBackupOverallStatus -IntegratedSecurity -SqlHost 'example.com' -IncludeDatabase 'ExampleDatabase','AnotherDatabase';
    [OK] Check package "MSSQL Backup"
    | 'status'=0;6;5 'size'=12664832b;; 'execution_time'=0s;; 'age'=493200s;; 'average_size'=6332416b;; 'status'=0;6;5 'size'=10110976b;;
    'execution_time'=0s;; 'age'=144000s;; 'average_size'=3370325.333333b;;
.PARAMETER TotalBackupSizeWarning
    Warning threshold for the total backupsize which represent a count of all backups
.PARAMETER TotalBackupSizeCritical
    Critical threshold for the total backupsize which represent a count of all backups
.PARAMETER AvgBackupSizeWarning
    Warning threshold for the average backupsize which represent an average backupsize of all backups
.PARAMETER AvgBackupSizeCritical
    Critical threshold for the average backupsize which represent an average backupsize of all backups
.PARAMETER LastBackupLogAgeWarning
    Warning threshold for the last log backup age, which returns the elapsed time since a database was last backupped
.PARAMETER LastBackupAgeWarning
    Warning threshold for the last log backup age, which returns the elapsed time since a database was last backupped
.PARAMETER LastBackupAgeCritical
    Critical threshold for the last backup age, which returns the elapsed time since a database was last backupped
.PARAMETER ExecutionTimeWarning
    Warning threshold for the execution time, which returns the elapsed time how long the backup process took
.PARAMETER ExecutionTimeCritical
    Critical threshold for the execution time, which returns the elapsed time how long the backup process took
.PARAMETER DatabaseStatusWarning
    Warning threshold for the database status:
        Online
        Restoring
        Recovering
        Recovering_Pending
        Suspect
        Emergency
        Offline
        Copying
        Offline_Secondary
.PARAMETER DatabaseStatusCritical
    Critical threshold for the database status:
        Online
        Restoring
        Recovering
        Recovering_Pending
        Suspect
        Emergency
        Offline
        Copying
        Offline_Secondary
.PARAMETER IncludeDatabase
    Specifies the database or databases which will be checked. Leave empty to fetch metrics from
    all databases on the given system
.PARAMETER SqlUsername
    The username for connecting to the MSSQL database
.PARAMETER SqlPassword
    The password for connecting to the MSSQL database as secure string
.PARAMETER SqlHost
    The IP address or FQDN to the MSSQL server to connect to
.PARAMETER SqlPort
    The port of the MSSQL server/instance to connect to with the provided credentials
.PARAMETER IntegratedSecurity
    Allows this plugin to use the credentials of the current PowerShell session inherited by
    the user the PowerShell is running with. If this is set and the user the PowerShell is
    running with can access to the MSSQL database you will not require to provide username
    and password
.PARAMETER NoPerfData
    Disables the performance data output of this plugin
.PARAMETER Verbosity
    Changes the behavior of the plugin output which check states are printed:
    0 (default): Only service checks/packages with state not OK will be printed
    1: Only services with not OK will be printed including OK checks of affected check packages including Package config
    2: Everything will be printed regardless of the check state
.INPUTS
    System.Array
.OUTPUTS
    System.String
.LINK
    https://github.com/Icinga/icinga-powershell-mssql
.NOTES
#>


function Invoke-IcingaCheckMSSQLBackupStatus
{
    param (
        $TotalBackupSizeWarning     = $null,
        $TotalBackupSizeCritical    = $null,
        $AvgBackupSizeWarning       = $null,
        $AvgBackupSizeCritical      = $null,
        $LastBackupLogAgeWarning    = $null,
        $LastBackupLogAgeCritical   = $null,
        $LastBackupAgeWarning       = $null,
        $LastBackupAgeCritical      = $null,
        $ExecutionTimeWarning       = $null,
        $ExecutionTimeCritical      = $null,
        [ValidateSet('Online', 'Restoring', 'Recovering', 'Recovering_Pending', 'Suspect', 'Emergency', 'Offline', 'Copying', 'Offline_Secondary')]
        $DatabaseStatusWarning      = 'Offline',
        [ValidateSet('Online', 'Restoring', 'Recovering', 'Recovering_Pending', 'Suspect', 'Emergency', 'Offline', 'Copying', 'Offline_Secondary')]
        $DatabaseStatusCritical     = 'Emergency',
        [array]$IncludeDatabase     = @(),
        [string]$SqlUsername,
        [securestring]$SqlPassword,
        [string]$SqlHost            = "localhost",
        [int]$SqlPort               = 1433,
        [switch]$IntegratedSecurity = $FALSE,
        [switch]$NoPerfData,
        [ValidateSet(0, 1, 2)]
        $Verbosity                  = 0
    )

    $LastBackupAgeWarning  = ConvertTo-SecondsFromIcingaThresholds $LastBackupAgeWarning;
    $LastBackupAgeCritical = ConvertTo-SecondsFromIcingaThresholds $LastBackupAgeCritical;
    $ExecutionTimeWarning  = ConvertTo-SecondsFromIcingaThresholds $ExecutionTimeWarning;
    $ExecutionTimeCritical = ConvertTo-SecondsFromIcingaThresholds $ExecutionTimeCritical;

    $SqlConnection         = Open-IcingaMSSQLConnection -Username $SqlUsername -Password $SqlPassword -Address $SqlHost -IntegratedSecurity:$IntegratedSecurity -Port $SqlPort;
    $BackupSet             = Get-IcingaMSSQLBackupOverallStatus -SqlConnection $SqlConnection;
    $InstanceName          = Get-IcingaMSSQLInstanceName -SqlConnection $SqlConnection;
    $CheckPackage          = New-IcingaCheckPackage -Name ([string]::Format('MSSQL Backup ({0})', $InstanceName)) -OperatorAnd -Verbose $Verbosity;

    Close-IcingaMSSQLConnection -SqlConnection $SqlConnection;

    foreach ($Backup in $BackupSet.Keys) {
        $BackupObject = $BackupSet[$Backup].backup;

        $DBPackage = New-IcingaCheckPackage -Name $Backup -OperatorAnd -Verbose $Verbosity;

        $DBPackage.AddCheck(
            (New-IcingaCheck -Name 'Size' -Unit 'b' -Value $BackupObject.TotalBackupSize).WarnOutOfRange($TotalBackupSizeWarning).CritOutOfRange($TotalBackupSizeCritical)
        );
        $DBPackage.AddCheck(
            (New-IcingaCheck -Name 'Average Size' -Unit 'b' -Value $BackupObject.AvgBackupSize).WarnOutOfRange($AvgBackupSizeWarning).CritOutOfRange($AvgBackupSizeCritical)
        );
        $DBPackage.AddCheck(
            (New-IcingaCheck -Name 'Log age' -Unit 's' -Value $BackupObject.LastBackupLogAge).WarnOutOfRange($LastBackupLogAgeWarning).CritOutOfRange($LastBackupLogAgeCritical)
        );
        $DBPackage.AddCheck(
            (New-IcingaCheck -Name 'Age' -Unit 's' -Value $BackupObject.LastBackupAge).WarnOutOfRange($LastBackupAgeWarning).CritOutOfRange($LastBackupAgeCritical)
        );
        $DBPackage.AddCheck(
            (New-IcingaCheck `
                -Name 'Execution Time' `
                -Unit 's' `
                -Value $BackupObject.ExecutionTime).WarnOutOfRange($ExecutionTimeWarning).CritOutOfRange($ExecutionTimeCritical)
        );
        $DBPackage.AddCheck(
            (New-IcingaCheck `
                -Name 'Status' `
                -Value $BackupObject.Status `
                -Translation $MSSQLProviderEnums.MSSQLDatabaseState `
                -ObjectExists $BackupObject.Status).WarnIfMatch($MSSQLProviderEnums.MSSQLDatabaseStateName[$DatabaseStatusWarning]).CritIfMatch($MSSQLProviderEnums.MSSQLDatabaseStateName[$DatabaseStatusCritical])
        );

        $CheckPackage.AddCheck($DBPackage);
    }

    return (New-IcingaCheckResult -Check $CheckPackage -Compile);
}