dbachecks

2.0.7

functions/Write-DbcTable.ps1

                                <#

.SYNOPSIS

Writes the result of Invoke-DbcCheck (with -PassThru) after Convert-DbcResult to a database table

.DESCRIPTION

After running Invoke-DbcCheck (With PassThru) and converting it to a datatable with Convert-DbcResult, this command

will write the results to a database table and will also write the current Checks to another table called dbachecksChecks

.PARAMETER SqlInstance

The Instance for the results

.PARAMETER SqlCredential

The SQL Credential for the instance if required

.PARAMETER Database

The database to write the results

.PARAMETER InputObject

The datatable from Convert-DbcResult

.PARAMETER Table

The name of the table for the results - will be created if it doesn't exist. By default it will be named CheckResults

.PARAMETER Schema

The schema for the table - defaults to dbo

.PARAMETER Truncate

Will truncate the existing table (if results go to a staging table for example)

.EXAMPLE

Invoke-DbcCheck -SqlInstance SQL2017N5 -Check AutoClose -Passthru | Convert-DbcResult -Label Beard-Check | Write-DbcTable -SqlInstance sql2017n5 -Database tempdb -Table newdbachecks

Runs the AutoClose check against SQL2017N5 and converts to a datatable with a label of Beard-Check and writes it to a table newdbachecks in tempdb on SQL2017N5 (NB Don't use tempdb!!)

.NOTES

Initial - RMS 28/12/2019

#>

function Write-DbcTable {

    [CmdletBinding(SupportsShouldProcess)]

    [OutputType([string])]

    Param(

        [Parameter(Mandatory)]

        [ValidateNotNull()]

        [string]$SqlInstance,

        [ValidateNotNull()]

        [PSCredential]$SqlCredential,

        [object]$Database,

        [Parameter(Mandatory, ValueFromPipeline)]

        # The pester results object

        [ValidateNotNull()]

        [object]$InputObject,

        [ValidateNotNullOrEmpty()]

        [string]$Table = 'CheckResults',

        [ValidateNotNullOrEmpty()]

        [string]$Schema = 'dbo',

        [switch]$Truncate

    )

    Write-PSFMessage "Testing we have a Test Results object" -Level Verbose

    if (-not $InputObject) {

        Write-PSFMessage "Uh-Oh - I'm really sorry - We don't have a Test Results Object" -Level Significant

        Write-PSFMessage "Did You forget the -PassThru parameter on Invoke-DbcCheck?" -Level Warning

        Return ''

    }

    Write-PSFMessage "Connecting to $SqlInstance" -Level Verbose

    $SqlInstanceSmo = Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential

    Write-PSFMessage "Checking for dbachecks table in $Database" -Level Verbose

    if (Get-DbaDbTable -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks) {

        if ($PSCmdlet.ShouldProcess("$schema.$database" , "On $SqlInstance - Update the dbachecksChecks tables ")) {

            Get-DbcCheck | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks -Schema $Schema -AutoCreateTable -Truncate

        }

    }

    else {

        if ($PSCmdlet.ShouldProcess("$schema.$database" , "On $SqlInstance - Add the dbachecksChecks tables ")) {

            Get-DbcCheck | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks -Schema $Schema -AutoCreateTable

        }

    }

    Write-PSFMessage "Checking for $Table in $Database" -Level Verbose

    if (Get-DbaDbTable -SqlInstance $SqlInstanceSmo -Database $Database -Table $Table) {

        Write-PSFMessage "We have $table already - moving on." -Level Verbose

    }

    else {

        if ($PSCmdlet.ShouldProcess("$schema.$database" , "Create a new table called $table ")) {

            # If specified table does not exists, create with specific datatypes to avoid nvarchar(max)

            $sqlTableCreation = @"

 CREATE TABLE [$schema].[$table](

     [Date] [datetime2](7) NOT NULL,

     [Label] [nvarchar](255) NULL,

     [Describe] [nvarchar](255) NULL,

     [Context] [nvarchar](255) NULL,

     [Name] [nvarchar](600) NULL,

     [Database] [nvarchar](255) NULL,

     [ComputerName] [nvarchar](255) NULL,

     [Instance] [nvarchar](255) NULL,

     [Result] [nvarchar](10) NULL,

     [FailureMessage] [nvarchar](MAX) NULL

 ) ON [PRIMARY]

 GO

 CREATE CLUSTERED INDEX CI_DATE ON [$schema].[$table]

 (

     [Date]

 ) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]

 GO

"@

            Invoke-DbaQuery -SqlInstance $SqlInstanceSmo -Database $Database -Query $sqlTableCreation

        }

    }

    if ($PSCmdlet.ShouldProcess("$Schema.$database" , "On $SqlInstance - Add dbachecks results to $Table ")) {

        $InputObject | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo  -Database $Database -Table $Table -Schema $Schema -AutoCreateTable -Truncate:$Truncate

    }

}

# SIG # Begin signature block

# MIINEAYJKoZIhvcNAQcCoIINATCCDP0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB

# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR

# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUegYAffTRCI+hKr04nGgnT1lL

# /aSgggpSMIIFGjCCBAKgAwIBAgIQAsF1KHTVwoQxhSrYoGRpyjANBgkqhkiG9w0B

# 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

# BgorBgEEAYI3AgEVMCMGCSqGSIb3DQEJBDEWBBRBq4Yyaxo2ZihLKGHzCApV8wzN

# YjANBgkqhkiG9w0BAQEFAASCAQBW1PSNLI/Kl12udbU1Q3TV2LOSD+EEC5wEWn9X

# Qx5l89esW1UwckAZO6SG6ItrqOATPXAU7W6EgP/QwH9BaigjdRAeI6Yeym4sDM8h

# 8jV8YPoMJXylEkgSsjZXeF66vu8Euk5WakE1tXfAsfYUumU/TRGo15yfNSqvkMdh

# U+M84XXiNVMHlu+0MPtvnxI9PoXUuB4lBsafjjmEkoqmVsl/Y2zn2zoZkDuly/DY

# vvau3Pl873m1ek2U7PtvBWuf2hTsWMIJWP2w9vlr5Kv1OSluBbsbSNdlDOlXYRSF

# 0WiID26B/9duWJDW0WOyp35+uztj0ZRghn4L6v7Xr+mmAx+y

# SIG # End signature block