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 DbaInstance -Database dbadatabase Runs the AutoClose check against SQL2017N5 and converts to a datatable with a label of Beard-Check and writes it to the dbadatabase on DbaInstance .EXAMPLE Import-DbcConfig -Path \\share\ProdMorningChecks.json Invoke-DbcCheck -SqlInstance $ProdSqlInstances -Passthru | Convert-DbcResult -Label Prod-Morning-Check | Write-DbcTable -SqlInstance DbaInstance -Database dbadatabase Loads the Prod Morning Checks Configuration and runs the checks against the SQL Instances converts to a datatable with a label of Prod-Morning-Check and writes it to the dbadatabase on DbaInstance .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 |