dbachecks

1.1.108

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" {

                    $jobsteps = $job.JobSteps | Where-Object { $_.SubSystem -eq "CmdExec" }

                    if ($jobsteps) {

                        $results = $jobsteps.Command.Split("@") | Where-Object { $_ -match "CleanupTime" }

                    }

                    else {

                        $results = $null    

                    }

                    It "Is the backup retention set to at least $Retention hours" {

                        if ($results) {

                            $hours = $results.split("=")[1].split(",").split(" ")[1]

                        }

                        $hours | Should -BeGreaterOrEqual $Retention -Because 'The backup retention needs to be correct'

                    }

                }

            }

        }

    }

}

# SIG # Begin signature block

# MIINEAYJKoZIhvcNAQcCoIINATCCDP0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB

# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR

# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUB+QG1pjDFsxFFp2tUlwlAD1I

# tTmgggpSMIIFGjCCBAKgAwIBAgIQAsF1KHTVwoQxhSrYoGRpyjANBgkqhkiG9w0B

# 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

# BgorBgEEAYI3AgEVMCMGCSqGSIb3DQEJBDEWBBSbq9SbTIR2QPadyT2koHVa2Bl8

# 3zANBgkqhkiG9w0BAQEFAASCAQCEnEh8TlPkzIfMoEgbVp4Ecd25fcR7tFUPFQSh

# 1uICtgzAFPKQ7oCgpnb0l4pM00AzhuVaCXYa7Ry3+eoXQrmfmyoIpmPwsBaIWIBu

# cw8V0BDH20w3/rTGCEeb0GLtD9eYIwZv4e1/EbH+48L0dj8opw801uk0N9p6BSCg

# vwylKeIuHzYD56A8RXwsvnjDf0bF2oF3/RZdM/oOjPI6DA1A7Iyi1EpfyQPFWz95

# XMNfXlD27ZzjhXCxefQzMZ2UsiY79JKYfZ5d+cckdHa414Th0NrhlGH9q4pw0uH0

# mpMqL9ZZmBsVa+l9JRCkKLLMUJzWT4uALQrKNvRO5CxVoK3+

# SIG # End signature block