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:

$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.

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.

#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

#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

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

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

$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:

#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)