SQL 数据库报告已修订

去年我写了一篇文章，解释了如何使用 SQLSERVER PSDrive 创建 HTML 报告，突出显示一些服务器和数据库信息。如果您想复习一下，可以在这里找到该文章。简而言之，您可以在客户端桌面上安装 SQL Server PowerShell 模块并使用它来管理远程服务器。
在我本周教授的 PowerShell 课程中，我添加了一些有关 SQL cmdlet 的内容，并有机会重新访问原始脚本。尽管原始版本旨在与远程服务器一起使用，但我有一些错误阻止了这种情况的发生。我还决定添加一些附加信息以及更多条件格式。例如，根据剩余的可用空间量，报告可能会将值格式化为黄色以指示警告，或以红色格式化以指示更严重的情况。

在 SQLSERVER PSdrive 中，数据库目录默认不显示任何系统数据库，除非您使用 -Force。

这是修改后的脚本：

#requires -version 4.0
#requires -module SQLPS


<#
Create a SQL Server database report
This script includes a graphic file which by default should be in the
same directory as this script. Otherwise, edit the $ImageFile variable. 

It is recommended that you import the SQLPS module first and then run 
this script.

Usage:
c:\scripts\SQLServerReport -computername CHI-SQL01 -includesystem -path c:\work\CHI-SQL01-DB.htm


#>

[cmdletbinding()]

Param(
[Parameter(Position=0,HelpMessage="Enter the name of a SQL server")]
[ValidateNotNullorEmpty()]
[Alias("CN")]
[string]$computername=$env:computername,
[Parameter(Position=1,HelpMessage="Enter the named instance such as Default")]
[ValidateNotNullorEmpty()]
[string]$Instance = "Default",
[switch]$IncludeSystem,
[ValidateNotNullorEmpty()]
[string]$Path="$env:temp\sqlrpt.htm",
[switch]$ShowReport
)


$scriptversion = "2.0"

Write-Verbose "Starting $($MyInvocation.Mycommand)"

#define the path to the graphic
$graphic = "db.png"
#the default location is the same directory as this script
$imagefile = Join-path -path (split-path $MyInvocation.InvocationName) -ChildPath $graphic

#define an empty array to hold all of the HTML fragments
$fragments=@("<br><br><br>")

#get uptime
Write-Verbose "Getting SQL Server uptime"
Try {
    #try to connect to the SQL server
    $starttime = Invoke-Sqlcmd -Query 'SELECT sqlserver_start_time AS StartTime FROM sys.dm_os_sys_info' -ServerInstance $computername -database master -ErrorAction Stop
}
Catch {
    Write-warning "Can't connect to $computername. $($_.exception.message)"
    #bail out
    Return
}

Write-Verbose "Getting SQL Version" 
$version = Invoke-Sqlcmd "Select @@version AS Version,@@ServerName AS Name" -ServerInstance $computername

#create an object
$uptime = New-Object -TypeName PSObject -Property @{
 StartTime = $starttime.Item(0)
 Uptime = (Get-Date)-$starttime.Item(0)
 Version = $version.Item(0).replace("`n","|")
}

$tmp = $uptime | ConvertTo-HTML -fragment -AS List
#replace "|" place holder with <br>"
$fragments += $tmp.replace("|","<br>")

#get services
Write-Verbose "Querying SQL services"
$services = Get-Service -DisplayName *SQL* -ComputerName $computername | 
Select Name,Displayname,Status

#add conditional formatting to display stopped services in yellow
[xml]$html = $services | ConvertTo-Html -fragment

#check each row, skipping the TH header row
for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if ($html.table.tr[$i].td[-1] -ne 'Running') {                                          
    $html.table.tr[$i].lastChild.setAttribute("class","warn") | Out-Null
  }  
} #for


$fragments += "<h3>SQL Services</h3>"
$fragments += $html.InnerXML

#get database information
#path to databases
$dbpath = "SQLServer:\SQL$computername$instance\databases"
Write-Verbose "Querying database information from $dbpath"

if ($IncludeSystem) {
    Write-Verbose "Including system databases"
    $dbs = Get-ChildItem -path $dbpath -Force
} else {
    $dbs = Get-ChildItem -path $dbpath
}

[xml]$html = $dbs | Select Name,
@{Name="SizeMB";Expression={$_.size}},
@{Name="DataSpaceMB";Expression={$_.DataSpaceUsage}},
@{Name="AvailableMB";Expression={$_.SpaceAvailable}},
@{Name="PercentFree";Expression={ [math]::Round((($_.SpaceAvailable/1kb)/$_.size)*100,2) }} | 
Sort PercentFree | ConvertTo-HTML -fragment

for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if (($html.table.tr[$i].td[-1] -as [double]) -le 15) {                                          
    $html.table.tr[$i].lastChild.SetAttribute("class","danger") | Out-Null
  }
  elseif (($html.table.tr[$i].td[-1] -as [double]) -le 25) {                                               
    $class.value = "warn"    
    $html.table.tr[$i].lastChild.SetAttribute("class","warn") | Out-Null
  }
}
$fragments += "<h3>Database Utilization</h3>"
$fragments += $html.InnerXml

$fragments += "<h3>Database Backup</h3>"
$fragments += $dbs | Select Name,Owner,CreateDate,Last*,RecoveryModel | ConvertTo-Html -Fragment

#volume usage
Write-Verbose "Querying system volumes"
$data = Get-CimInstance win32_volume -filter "drivetype=3" -ComputerName $computername

$drives = foreach ($item in $data) {
    $prophash = [ordered]@{
    Drive = $item.DriveLetter
    Volume = $item.DeviceID
    Compressed = $item.Compressed
    SizeGB = $item.capacity/1GB -as [int]
    FreeGB = "{0:N4}" -f ($item.Freespace/1GB )
    PercentFree = [math]::Round((($item.Freespace/$item.capacity) * 100),2)
    }

    #create a new object from the property hash
    New-Object PSObject -Property $prophash
}

[xml]$html = $drives | ConvertTo-Html -fragment

#check each row, skipping the TH header row
for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if (($html.table.tr[$i].td[-1] -as [int]) -le 25) {                                          
    $html.table.tr[$i].lastChild.SetAttribute("class","danger") | Out-Null
  }
  elseif (($html.table.tr[$i].td[-1] -as [int]) -le 35) {                                               
    $class.value = "warn"    
    $html.table.tr[$i].lastChild.SetAttribute("class","warn") | Out-Null
  }
}

$fragments += "<h3>Volume Utilization</h3>"
$fragments += $html.innerxml

#define the HTML style
Write-Verbose "preparing report"

#encode the graphic file to embed into the HTML
$ImageBits = [Convert]::ToBase64String((Get-Content $imagefile -Encoding Byte))
$ImageHTML = "<img src=data:image/png;base64,$($ImageBits) alt='db utilization'/>"

#define a here string for the html header
$head = @"
<style>
body { background-color:#FAFAFA;
       font-family:Arial;
       font-size:12pt; }
td, th { border:1px solid black; 
         border-collapse:collapse; }
th { color:white;
     background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
tr:nth-child(odd) {background-color: lightgray}
table { margin-left:50px; }
img
{
float:left;
margin: 0px 25px;
}
.danger {background-color: red}
.warn {background-color: yellow}
</style>
$imagehtml
<br><br><br>
<H2>SQL Server Report: $($version.name)</H2>
<br>
"@

#HTML to display at the end of the report
$footer = @"
<br>
<i>
Date&nbsp;&nbsp;&nbsp;&nbsp;: $(Get-Date)<br>
Author&nbsp;: $env:USERDOMAIN$env:username<br>
Version: $scriptVersion<br>
</i>
"@

#create the HTML document
ConvertTo-HTML -Head $head -Body $fragments -PostContent $footer |
Out-File -FilePath $path -Encoding ascii

if ($ShowReport) {
    #open the finished report
    Write-Verbose "Opening report $path"
    Invoke-Item $path
}

Write-Verbose "Ending $($MyInvocation.Mycommand)"

我的脚本包含一个图形文件以使其美观。你可以下载我的图形文件db.png。将图形放在与脚本相同的目录中。或者修改脚本以使用您自己的图形。然后您可以运行这样的命令来生成 HTML 报告。

c:\scripts\sqlserverreport.ps1 -computername chi-sql01 -IncludeSystem -path s:\chi-sql01.htm -verbose

此脚本缺少备用凭据的规定，并假设您正在运行的帐户具有必要的 SQL 权限。完成后您可以获得这样的报告。

我希望您能让我知道您的想法以及您是否认为这有用。