tests/Test-SqlDiskSpace.ps1
|
function Test-SqlDiskSpace { [CmdletBinding()] param ( [parameter()][string] $TestName = "SQL Instance Disk Space", [parameter()][string] $TestGroup = "operation", [parameter()][string] $TestCategory = "SQL", [parameter()][string] $Description = "Check disk space status on SQL Instance", [parameter()][hashtable] $ScriptParams ) try { $startTime = (Get-Date) [int]$MaxPctUsed = Get-CmHealthDefaultValue -KeySet "siteservers:DiskSpaceMaxPercent" -DataSet $CmHealthConfig Write-Log -Message "MaxPctUsed = $MaxPctUsed" [System.Collections.Generic.List[PSObject]]$tempdata = @() # for detailed test output to return if needed $stat = "PASS" # do not change this $except = "WARNING" $msg = "No issues found" # do not change this either $issues = 0 Write-Log -Message "requesting list of DP servers" $query = "select ServerName from dbo.v_DistributionPointInfo order by ServerName" [array]$dplist = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $query | Select-Object -ExpandProperty ServerName Write-Log -Message "$($dplist.Count) DP server names returned" [string]$myFQDN=(Get-CimInstance Win32_ComputerSystem).DNSHostName+"."+(Get-CimInstance Win32_ComputerSystem).Domain [int]$index=1 foreach ($dp in $dplist) { $res = $null; $cs = $null if ($dp -ne $myFQDN) { Write-Log -Message "connecting to remote DP [$index of $($dplist.Count)]: $dp" $cs = New-CimSession -Credential $ScriptParams.Credential -Authentication Negotiate -ComputerName $dp -ErrorAction SilentlyContinue if ($null -ne $cs) { $res = @(Get-CimInstance -CimSession $cs -ClassName Win32_LogicalDisk -Filter "DriveType = 3" -ErrorAction SilentlyContinue) } else { $res = $null } } else { Write-Log -Message "connecting to local DP [$index of $($dplist.Count)]: $dp" $res = @(Get-CimInstance -ClassName Win32_LogicalDisk -Filter "DriveType = 3") } if ($res.Count -gt 0) { foreach ($disk in $res) { $size = $disk.Size $free = $disk.FreeSpace $used = $size - $free $pct = $([math]::Round($used / $size, 1)) * 100 if ($pct -gt $MaxPctUsed) { $tempData.Add( [pscustomobject]@{ Computer = $($dp) Drive = $($disk.DeviceID) SizeGB = [math]::Round($size / 1GB, 1) PctUsed = $pct } ) $stat = $except $issues++ } else { $tempData.Add( [pscustomobject]@{ Computer = $($dp) Drive = $($disk.DeviceID) SizeGB = [math]::Round($size / 1GB, 1) PctUsed = $pct } ) } } # foreach if ($issues -gt 0) { $msg = "$issues issues were found" } } else { Write-Warning "DP disk information is not available: $dp" $tempData.Add( [pscustomobject]@{ Computer = $($dp) Drive = $null SizeGB = $null PctUsed = $null } ) } $index++ } # foreach } catch { $stat = 'ERROR' $msg = $_.Exception.Message -join ';' } finally { Set-CmhOutputData } } |