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