Wrapper for DBAChecks

Working with DBAChecks, I was wanting a bit more control as well as some metrics on the execution. Here's what I came up with:

Generic variable initialization:

?

1 2 3 4 5 6

$ExcludeServer = @() $ExcludeGroup = @() $timeData = @()   $PowerBiDataPath = '\\Shared Network path for the data\dbachecks\data' $cmsServer = "My CMS Server"

Defining the exclusions for the tests. I wanted to be able to exclude specific tags on a per server or ServerGroup level.

?

1 2 3 4 5 6 7

Class Exclusion {     [ValidateNotNullOrEmpty()][String]$Name     [string[]]$Tags } $ExcludeGroup += [Exclusion]@{name="DBA Sandboxes"} #Exclude entire group $ExcludeGroup += [Exclusion]@{name="Dev"; tags=@("Backup")} # Exclude backup checks on the Dev group

Excluding the servers based on a wildcard, in this case, it's my DR servers.

?

1 2 3 4 5 6 7

#Exclude the DR servers Get-DbaRegisteredServer $cmsServer |         where-object {$PSItem.name -Like '*-DR' -or $PSItem.name -Like '*-DR-*'} |     Sort-Object -Unique |     ForEach-Object {         $ExcludeServer += [Exclusion]@{name = $PSItem.name}     }

Getting the list of groups to test from the CMS server

?

1 2 3 4 5

#Waiting for this to get added to dbatools.io #(Get-DbaRegisteredServerGroup -SqlInstance $cmsServer -ExcludeGroup $ExcludeGroup).name | sort-object -Unique $groups = ((Get-DbaRegisteredServersStore -SqlInstance $cmsServer).DatabaseEngineServerGroup.ServerGroups).name |     Where-Object {$PSItem -notin ($ExcludeGroup | where Tags -eq $null).name} |     Sort-Object -Unique

Define the tags that you want to test for

?

1	$tags = 'AutoClose', 'AutoShrink', 'DAC', 'TempDbConfiguration', 'DatafileAutoGrowthType'

Here's the real meat of this script...

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

$groups |        ForEach-Object {         $groupName = $PSItem           # Get the list of SQL instances from the CMS sever, except those that are excluded         $sqlinstances = Get-DbaRegisteredServer -SqlInstance $cmsServer -Group $groupName |             where name -NotIn ($ExcludeServer | where Tag -eq $null ).name              $tags | ForEach-Object {             $tag = $PSItem               $obj =  [pscustomobject]@{                            ServerGroup = $groupName                 NumServers = $sqlinstances.Count                 Tag = $tag                 InvokeStartTime = Get-Date                 InvokeCompleteTime = $null                 WriteResultsTime = $null                 TestExecution = $null                 InvokeDuration = $null                 ResultsDuration = $null                 PassedCount       = $null                 FailedCount       = $null                 SkippedCount      = $null                 PendingCount      = $null                 InconclusiveCount = $null                            }                           $results = Invoke-DbcCheck -SqlInstance $sqlinstances -tags $tag -PassThru -Show Fails             $obj.TestExecution     = $results.time             $obj.PassedCount       = $results.PassedCount                  $obj.FailedCount       = $results.FailedCount                  $obj.SkippedCount      = $results.SkippedCount                 $obj.PendingCount      = $results.PendingCount                 $obj.InconclusiveCount = $results.InconclusiveCount               $obj.InvokeCompleteTime = Get-Date               $results | Update-DbcPowerBiDataSource -Enviornment $groupName -Append -Path $PowerBiDataPath             $obj.WriteResultsTime = Get-Date                     $obj.InvokeDuration = New-TimeSpan -Start $obj.InvokeStartTime -End $obj.InvokeCompleteTime             $obj.ResultsDuration = New-TimeSpan -Start $obj.InvokeCompleteTime -End $obj.WriteResultsTime               $timeData += $obj                } # $tags | ForEach-Object               } # $groups | ForEach-Object   $timeData |     Select-Object ServerGroup, NumServers, tag, InvokeDuration,  TestExecution, ResultsDuration |     ft -AutoSize

Send the timing analysis via email as an HTML table:

?

1 2 3 4 5 6

#https://gallery.technet.microsoft.com/scriptcenter/bd0c8d87-466f-4488-92a2-0f726cb6f4cd . "$PSScriptRoots\Functions\Send-EmailHTML.ps1"   Send-EmailHTML -To $ToEmail -from $FromEmail -Subject "DBAChecks Has updated PowerBI" `     -BodyAsArray ($timeData |         Select-Object ServerGroup, NumServers, tag, TotalCount, PassedCount, FailedCount, InvokeDuration,  TestExecution, ResultsDuration)