checks/MaintenanceSolution.Tests.ps1

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

Describe "Ola maintenance solution installed" -Tags OlaInstalled, $filename{
    $OlaSPs = @('CommandExecute', 'DatabaseBackup', 'DatabaseIntegrityCheck', 'IndexOptimize')
    $oladb = Get-DbcConfigValue policy.ola.database
    (Get-SqlInstance).ForEach{        
        $db = Get-DbaDatabase -SqlInstance $PSItem -Database $oladb
        Context "Checking the CommandLog table on $psitem"{            
            It "The CommandLog table exists in $oladb on $PSItem" {
                @($db.tables | Where-Object name -eq "CommandLog").Count | Should -Be 1 -Because 'The command log table is required'
            }
        }
        Context "Checking the Ola Stored Procedures on $psitem" {
            It "The stored procedures exists in $oladb on $PSItem" {
                ($db.StoredProcedures | Where-Object {$PSItem.schema -eq 'dbo' -and $PSItem.name -in $OlaSPs} | Measure-Object).Count | Should -Be $OlaSPs.Count -Because 'The stored procedures are required for Olas jobs to run'
            }
        }
    }
}

$jobnames = @()
$jobnames += [pscustomobject]@{JobName='DatabaseBackup - SYSTEM_DATABASES - FULL';      prefix='SystemFull'}
$jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - FULL';        prefix='UserFull'}
$jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - DIFF';        prefix='UserDiff'}
$jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - LOG';         prefix='UserLog'}
$jobnames += [pscustomobject]@{JobName='CommandLog Cleanup';                            prefix='CommandLog'}
$jobnames += [pscustomobject]@{JobName='DatabaseIntegrityCheck - SYSTEM_DATABASES';     prefix='SystemIntegrityCheck'}
$jobnames += [pscustomobject]@{JobName='DatabaseIntegrityCheck - USER_DATABASES';       prefix='UserIntegrityCheck'}
$jobnames += [pscustomobject]@{JobName='IndexOptimize - USER_DATABASES';                prefix='UserIndexOptimize'}
$jobnames += [pscustomobject]@{JobName='Output File Cleanup';                           prefix='OutputFileCleanup'}
$jobnames += [pscustomobject]@{JobName='sp_delete_backuphistory';                       prefix='DeleteBackupHistory'}
$jobnames += [pscustomobject]@{JobName='sp_purge_jobhistory';                           prefix='PurgeJobHistory'}

$jobnames | ForEach-Object {    
    $JobPrefix = $psitem.prefix
    $tagname = "Ola$($JobPrefix)"
    $JobName = $PSItem.Jobname    
    $Enabled = Get-DbcConfigValue "policy.ola.$($JobPrefix)enabled"
    $Scheduled = Get-DbcConfigValue "policy.ola.$($JobPrefix)scheduled"    
    $Retention = Get-DbcConfigValue "policy.ola.$($JobPrefix)retention"     

    #Write-PSFMessage -Level Host -Message "$jobname / $JobPrefix / $tagname"

    Describe "Ola - $Jobname" -tags $tagname, OlaJobs, $filename {
        (Get-SqlInstance).ForEach{
            $job = Get-DbaAgentJob -SqlInstance $PSItem -Job $JobName
            Context  "Is job enabled on $PSItem" {
                It "$JobName Should Be enabled - $Enabled " {
                    $job.IsEnabled | Should -Be $Enabled -Because 'If the job is not enabled it will not run'
                }
            }
            Context "Is job scheduled on $PSItem" {
                It "$JobName Should Be scheduled - $Scheduled " {
                    $job.HasSchedule | Should -Be $Scheduled -Because 'If the job is not scheduled it will not run'
                }
                It "$($JobName) schedules Should Be enabled - $Scheduled" {
                    $results = ($job.JobSchedules | Where-Object IsEnabled | Measure-Object ).Count -gt 0
                    $results | Should -BeGreaterThan 0 -Because 'If the schedule is not enabled the jobs will not run'
                }
            }      

            if ($Retention) {
                Context "Checking the backup retention on $psitem" {
                    $results = (($job.JobSteps | Where-Object {$_.SubSystem -eq "CmdExec"}).Command.Split("@") | Where-Object {$_ -match "CleanupTime"})
                    It "Is the backup retention set to at least $Retention hours" {                   
                        $results.split("=")[1].split(",").split(" ")[1] | Should -BeGreaterOrEqual $Retention -Because 'The backup retention needs to be correct'
                    }
                }
            }
        }
    }    
}

# SIG # Begin signature block
# MIINEAYJKoZIhvcNAQcCoIINATCCDP0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUFF1a7Kl5NCjU6UYr8M2CAkH5
# aIGgggpSMIIFGjCCBAKgAwIBAgIQAsF1KHTVwoQxhSrYoGRpyjANBgkqhkiG9w0B
# AQsFADByMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYD
# VQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYDVQQDEyhEaWdpQ2VydCBTSEEyIEFz
# c3VyZWQgSUQgQ29kZSBTaWduaW5nIENBMB4XDTE3MDUwOTAwMDAwMFoXDTIwMDUx
# MzEyMDAwMFowVzELMAkGA1UEBhMCVVMxETAPBgNVBAgTCFZpcmdpbmlhMQ8wDQYD
# VQQHEwZWaWVubmExETAPBgNVBAoTCGRiYXRvb2xzMREwDwYDVQQDEwhkYmF0b29s
# czCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAI8ng7JxnekL0AO4qQgt
# Kr6p3q3SNOPh+SUZH+SyY8EA2I3wR7BMoT7rnZNolTwGjUXn7bRC6vISWg16N202
# 1RBWdTGW2rVPBVLF4HA46jle4hcpEVquXdj3yGYa99ko1w2FOWzLjKvtLqj4tzOh
# K7wa/Gbmv0Si/FU6oOmctzYMI0QXtEG7lR1HsJT5kywwmgcjyuiN28iBIhT6man0
# Ib6xKDv40PblKq5c9AFVldXUGVeBJbLhcEAA1nSPSLGdc7j4J2SulGISYY7ocuX3
# tkv01te72Mv2KkqqpfkLEAQjXgtM0hlgwuc8/A4if+I0YtboCMkVQuwBpbR9/6ys
# Z+sCAwEAAaOCAcUwggHBMB8GA1UdIwQYMBaAFFrEuXsqCqOl6nEDwGD5LfZldQ5Y
# MB0GA1UdDgQWBBRcxSkFqeA3vvHU0aq2mVpFRSOdmjAOBgNVHQ8BAf8EBAMCB4Aw
# EwYDVR0lBAwwCgYIKwYBBQUHAwMwdwYDVR0fBHAwbjA1oDOgMYYvaHR0cDovL2Ny
# bDMuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwNaAzoDGGL2h0
# dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9zaGEyLWFzc3VyZWQtY3MtZzEuY3JsMEwG
# A1UdIARFMEMwNwYJYIZIAYb9bAMBMCowKAYIKwYBBQUHAgEWHGh0dHBzOi8vd3d3
# LmRpZ2ljZXJ0LmNvbS9DUFMwCAYGZ4EMAQQBMIGEBggrBgEFBQcBAQR4MHYwJAYI
# KwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRpZ2ljZXJ0LmNvbTBOBggrBgEFBQcwAoZC
# aHR0cDovL2NhY2VydHMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0U0hBMkFzc3VyZWRJ
# RENvZGVTaWduaW5nQ0EuY3J0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQELBQAD
# ggEBANuBGTbzCRhgG0Th09J0m/qDqohWMx6ZOFKhMoKl8f/l6IwyDrkG48JBkWOA
# QYXNAzvp3Ro7aGCNJKRAOcIjNKYef/PFRfFQvMe07nQIj78G8x0q44ZpOVCp9uVj
# sLmIvsmF1dcYhOWs9BOG/Zp9augJUtlYpo4JW+iuZHCqjhKzIc74rEEiZd0hSm8M
# asshvBUSB9e8do/7RhaKezvlciDaFBQvg5s0fICsEhULBRhoyVOiUKUcemprPiTD
# xh3buBLuN0bBayjWmOMlkG1Z6i8DUvWlPGz9jiBT3ONBqxXfghXLL6n8PhfppBhn
# daPQO8+SqF5rqrlyBPmRRaTz2GQwggUwMIIEGKADAgECAhAECRgbX9W7ZnVTQ7Vv
# lVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdp
# Q2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNVBAMTG0Rp
# Z2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEwMjIxMjAwMDBaFw0yODEw
# MjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMx
# GTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNI
# QTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEiMA0GCSqGSIb3DQEBAQUA
# A4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7RZmxOttE9X/lqJ3bMtdx
# 6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p0WfTxvspJ8fTeyOU5JEj
# lpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj6YgsIJWuHEqHCN8M9eJN
# YBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grkV7tKtel05iv+bMt+dDk2
# DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHyDxL0xY4PwaLoLFH3c7y9
# hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMBAAGjggHNMIIByTASBgNV
# HRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjATBgNVHSUEDDAKBggrBgEF
# BQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRp
# Z2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2NhY2VydHMuZGlnaWNlcnQu
# Y29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCBgQYDVR0fBHoweDA6oDig
# NoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9v
# dENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0
# QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgGCmCGSAGG/WwAAgQwKjAo
# BggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAKBghghkgB
# hv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHwYDVR0jBBgwFoAU
# Reuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQELBQADggEBAD7sDVoks/Mi
# 0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q3yBVN7Dh9tGSdQ9RtG6l
# jlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/kLEbBw6RFfu6r7VRwo0k
# riTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dcIFzZcbEMj7uo+MUSaJ/P
# QMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6dGRrsutmQ9qzsIzV6Q3d
# 9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT+hKUGIUukpHqaGxEMrJm
# oecYpJpkUe8xggIoMIICJAIBATCBhjByMQswCQYDVQQGEwJVUzEVMBMGA1UEChMM
# RGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYDVQQD
# EyhEaWdpQ2VydCBTSEEyIEFzc3VyZWQgSUQgQ29kZSBTaWduaW5nIENBAhACwXUo
# dNXChDGFKtigZGnKMAkGBSsOAwIaBQCgeDAYBgorBgEEAYI3AgEMMQowCKACgACh
# AoAAMBkGCSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwGCisGAQQBgjcCAQsxDjAM
# BgorBgEEAYI3AgEVMCMGCSqGSIb3DQEJBDEWBBRO8745TVId71Z+AmQnKDN2hmCy
# ojANBgkqhkiG9w0BAQEFAASCAQA5ErQIlM+av+hWFvA5DQnu+MerGAdzk9NFCmbr
# UXkHWZyCFmqPDMBZxhrDxm0WcoqsOTBYeSFHjcmNNIJlpjoeaYN+S40MQOznjnsu
# ORB5b85q46P67auO1Q3/o15B6TvKDfF8oYEjYKyYykw9A9fKbIfsQuNtcPyeKf1u
# qBXMFO9ZhoxjIhU74odRu0AQes0msRECjaDNCokHTMcHfdHo9dRtSTaFWRTvb7Lc
# gC3C1nL3VyQYsb3515oBmSuUXXl8tUqh3BWLRMNWER/HDqoCChb5ofUzFsIymaGG
# oHeMdBNIX2uHlCCLhxMsd8iM7w59xNGLalidggzq8goXqEe/
# SIG # End signature block